Lookup Vector > Lookup Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
Thanks Duke, your solution works a treat.


Duke Carey said:
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
Click to expand...
 
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!
 
Alec said:
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
Click to expand...

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