Suggestion to have lookup find the next nearest high number in a range. Use
should be able to select whether lookup finds the next higher or next lower
number if an exact match is not found.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

If your data is sorted in descending order, MATCH can return the smallest
number that is larger than the lookup value (you can use INDEX/MATCH to
perform the lookup). If your data is sorted in ascending order, you could
accomplish this with a formula posted by Harlen Grove

Assuming
A1:B5 = table containing data
C1 = lookup value

=INDEX(B1:B5,MATCH(C1,A1:A5)+(VLOOKUP(C1,A1:A5,1)< C1))

"luv2fly" wrote:

Suggestion to have lookup find the next nearest high number in a range. Use
should be able to select whether lookup finds the next higher or next lower
number if an exact match is not found.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

Hi JMB,

When I use your index formula since I cannot sort data in descending order I
get a #N/A error. Do you know why this is happening?

Thanks

"JMB" wrote:

If your data is sorted in descending order, MATCH can return the smallest
number that is larger than the lookup value (you can use INDEX/MATCH to
perform the lookup). If your data is sorted in ascending order, you could
accomplish this with a formula posted by Harlen Grove

Assuming
A1:B5 = table containing data
C1 = lookup value

=INDEX(B1:B5,MATCH(C1,A1:A5)+(VLOOKUP(C1,A1:A5,1)< C1))

"luv2fly" wrote:

Suggestion to have lookup find the next nearest high number in a range. Use
should be able to select whether lookup finds the next higher or next lower
number if an exact match is not found.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming