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
iegAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
You can use this formula with INDEX and MATCH, assuming lookup value in E3 as per fp's setup

=INDEX(B2:B4,MATCH(TRUE,INDEX(A2:A4>=E3,0),0))

If there is no equal or higher value you get #N/A

The second INDEX function isn't strictly necessary - I included it so that you don't need to "array enter" the formula. This version will work too

=INDEX(B2:B4,MATCH(TRUE,A2:A4>=E3,0))

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
Rgonzo1971Commented:
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
0
 
[ fanpages ]IT Services ConsultantCommented:
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
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
iegAuthor Commented:
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.
0
 
barry houdiniCommented:
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
0
 
iegAuthor Commented:
Many thanks - works a treat
Andy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.