Lookup Vector > Lookup Value

Hi,

CABLE SIZE CAPACITY
1.5 18.0
2.5 24
4 31
6 41
10 56

When I use the Lookup Function to find a Cable Size to match the Capacity
Value, say 35, the Lookup Function returns 4, which is the Lookup Vector that
is less or equal to the Lookup Value. I need the Lookup Vector to be greater
than or equal to the Lookup Value, ie 6 in this case.

Any Ideas.

Thanks
Alec

One way:

Restructure your table like so

CAPACITY CABLE SIZE Min Size
- - 1.50
18 1.50 2.50
24 2.50 4.00
31 4.00 6.00
41 6.00 10.00
56 10.00 ??


Assuming the table is in A1:C7, including the headers, use this formula

=IF(ISNA(VLOOKUP(A10,$A$2:$C$7,1,0)),VLOOKUP(A10,$ A$2:$C$7,3),VLOOKUP(A10,$A$2:$C$7,2,0))

Which basically looks for an exact match first. If it's there, it uses your
Cable Size. If not, it uses the Min Size



"Alec Kolundzic" wrote in message
...
Hi,

CABLE SIZE CAPACITY
1.5 18.0
2.5 24
4 31
6 41
10 56

When I use the Lookup Function to find a Cable Size to match the Capacity
Value, say 35, the Lookup Function returns 4, which is the Lookup Vector
that
is less or equal to the Lookup Value. I need the Lookup Vector to be
greater
than or equal to the Lookup Value, ie 6 in this case.

Any Ideas.

Thanks
Alec

Lets say your table above is in range A1:B5, and the lookup value is in
cell A9, then use the following formula:
=INDEX(A1:A5,MATCH(A9,B1:B5,1)+IF(ISNUMBER(MATCH(A 9,B1:B5,0)),0,1))

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378021

Thanks Duke, your solution works a treat.


"Duke Carey" wrote:

One way:

Restructure your table like so

CAPACITY CABLE SIZE Min Size
- - 1.50
18 1.50 2.50
24 2.50 4.00
31 4.00 6.00
41 6.00 10.00
56 10.00 ??


Assuming the table is in A1:C7, including the headers, use this formula

=IF(ISNA(VLOOKUP(A10,$A$2:$C$7,1,0)),VLOOKUP(A10,$ A$2:$C$7,3),VLOOKUP(A10,$A$2:$C$7,2,0))

Which basically looks for an exact match first. If it's there, it uses your
Cable Size. If not, it uses the Min Size



"Alec Kolundzic" wrote in message
...
Hi,

CABLE SIZE CAPACITY
1.5 18.0
2.5 24
4 31
6 41
10 56

When I use the Lookup Function to find a Cable Size to match the Capacity
Value, say 35, the Lookup Function returns 4, which is the Lookup Vector
that
is less or equal to the Lookup Value. I need the Lookup Vector to be
greater
than or equal to the Lookup Value, ie 6 in this case.

Any Ideas.

Thanks
Alec

Another way...

Assuming that a 'Capacity' greater than 56 is not needed, try the
following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MIN(IF(B2:B6=C1,A2:A6))

....where C1 contains your 'Capacity Value'.

Hope this helps!

In article ,
"Alec Kolundzic" wrote:

Hi,

CABLE SIZE CAPACITY
1.5 18.0
2.5 24
4 31
6 41
10 56

When I use the Lookup Function to find a Cable Size to match the Capacity
Value, say 35, the Lookup Function returns 4, which is the Lookup Vector that
is less or equal to the Lookup Value. I need the Lookup Vector to be greater
than or equal to the Lookup Value, ie 6 in this case.

Any Ideas.

Thanks
Alec

Alec Kolundzic wrote:
Hi,

CABLE SIZE CAPACITY
1.5 18.0
2.5 24
4 31
6 41
10 56

When I use the Lookup Function to find a Cable Size to match the Capacity
Value, say 35, the Lookup Function returns 4, which is the Lookup Vector that
is less or equal to the Lookup Value. I need the Lookup Vector to be greater
than or equal to the Lookup Value, ie 6 in this case.

Any Ideas.

Thanks
Alec



If the table (A1:B6) is sorted on CAPACITY...

=INDEX($A$2:$A$6,MATCH(C2,$B$2:$B$6,1)+(LOOKUP(C2, $B$2:$B$6)C2))

Thanks Mangesh, your solution works a treat.

"mangesh_yadav" wrote:


Lets say your table above is in range A1:B5, and the lookup value is in
cell A9, then use the following formula:
=INDEX(A1:A5,MATCH(A9,B1:B5,1)+IF(ISNUMBER(MATCH(A 9,B1:B5,0)),0,1))

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=378021