Lookup Vector > Lookup Value

[Guest]

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

 

[Duke Carey]

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

 

[mangesh_yadav]

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(A9,B1:B5,0)),0,1))

Mangesh

 

[Guest]

Thanks Duke, your solution works a treat.

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

 

[Domenic]

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!

 

[Aladin Akyurek]

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))

 

[Guest]

Thanks Mangesh, your solution works a treat.