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:
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!
I have a data table that looks something like this:
Column S | Column T |
160000 | 2.499 |
165000 | 2.563 |
170000 | 2.628 |
175000 | 2.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!