Index-Match Nearest Greater Than

Joecam

Member
Joined
May 22, 2014
Messages
41
Reaction score
0
Points
6
Excel Version(s)
365
Hi all, I'm looking for some help on a formula that I just can't get working properly.

I have a data table that looks something like this:
Column SColumn T
1600002.499
1650002.563
1700002.628
1750002.692

In cell 'XI1'!B2 I am storing the value to be looked up - 168000, for example.

The formula I'm using is: =INDEX('Data Form'!T3:T37,MATCH('XI1'!B2,'Data Form'!S3:S37,1))
This formula is working as expected, and returning the value of 2.563.

However, I need to find the next largest number in column T (2.628) so that I can perform work on it.
When I switch this formula to =INDEX('Data Form'!T3:T37,MATCH('XI1'!B2,'Data Form'!S3:S37,-1))
I get a #N/A error.
Stepping into the formula, the error indicates 'XI1'!B2 "The cell currently being evaluated contains a constant."

Any ideas on what I'm doing wrong? Am I just using the wrong formula for what I want to do?

Any assistance would be greatly appreciated!
 
With the -1 match type the lookup array must be sorted descending. Perhaps try =INDEX('Data Form'!T3:T37,MATCH('XI1'!B2,'Data Form'!S3:S37,1)+1)
 
That worked perfectly, thank you so much for your assistance!
 
Back
Top