asked on

lookup next highest value

Hi
I want to be able to enter a value in a cell and then have a formula that will lookup a value in a range - easy enough. But if the value I enter is between two of the lookup numbers then I want it to return the value associated with the larger of the numbers.
e.g.
Length Size
100 10
125 12
150 15

Enter a Length of 100 and VLookup will return a size of 10
If I enter a Length of 110 I want it to return a size of 12.

Help greatly received
Andy

Rgonzo1971

Hi

pls use

=INDEX(B2:B4,MATCH(D2,A2:A4,-1))

Open in new window

but your data has to be in descending order
MatchIndex.xlsx
Regards

[ fanpages ]

Hi Andy,

I can achieve the result using INDEX, MATCH, & LOOKUP.

If the sample data above is in the range [A1:B4], so that cell [A1] is "Length" & [B1] is "Size", & the data starts on row 2, & the value to be queried is in cell [E3], then this is the "VLOOKUP" formula that may be placed in any other cell to return the appropriate "Size":

=IF(E3<A2,B2,INDEX(B2:B4,MATCH(E3,A2:A4,1)+(LOOKUP(E3,A2:A4)<E3)))

Within the attached workbook, this formula is in cell [E4].

Note that a value less than 100 (cell [A2]) returns [B2], but a value greater than 150 (cell [A4]) will return #REF!.

BFN,

fp.
Q-28282930.xls

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ieg

ASKER

Thanks to you all - I've accepted Barry's "array formula" answer because it is the neatest
I just need to do some work to stop the #N/A if I select a number out of the range.

barry houdini

Assuming Excel 2007 or later you can add an IFERROR function to deal with #N/A, e.g.

=IFERROR(INDEX(B2:B4,MATCH(TRUE,A2:A4>=E3,0)),"No match")

regards, barry

ieg

ASKER

Many thanks - works a treat
Andy