• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

lookup next highest value

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.
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
1 Solution

pls use


Open in new window

but your data has to be in descending order
[ 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":


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!.


barry houdiniCommented:
You can use this formula with INDEX and MATCH, assuming lookup value in E3 as per fp's setup


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


confirmed with CTRL+SHIFT+ENTER

regards, barry
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
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
iegAuthor Commented:
Many thanks - works a treat
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now