Look-up a value within an interval
Thank you, Tom. However, the result that comes back is Result 4 when it
should be Result 2. I've also tried it with different references, but the
result of the Address portion of the formula comes back with a reference a
few rows below what it should be. Would you have any idea why that would be
happening?
"Tom Hutchins" wrote:
I'm sure there are more elegant solutions, but try this...
Assuming your lookup data is A1:D10, the type of interval (ARC) to look up
is in A15, and the value to look up is in A16:
In the cell where you want to return the answer, enter
=INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),--($C$2:$C$10=A16),ROW($A$2:$A$10)),4))
Adjust the cell references as needed.
Hope this helps,
Hutch
"Christine" wrote:
I would be very grateful if someone could provide me with a formula that will
look up the closest minimum and maximum based on a value and bring back the
contents of a cell to the right. In the example below, I want the formula to
look up 9,923, within the ARC intervals, and bring back Result 2.
ARC 9,923 Formula will bring back Result 2
A B C D
Minimum Maximum
1 ARC 0 10,000 Result 1
2 ARC 4,000 10,000 Result 2
3 ARC 10,000 9,999,999 Result 3
4 ARD 0 10,000 Result 4
5 ARD 10,000 9,999,999 Result 5
6 DCC 0 15,000 Result 6
7 DCC 4,000 9,999,999 Result 7
8 DCC 15,000 Result 8
9 DCD 0 9,999,999 Result 9
Thank you in advance for your help.
Chris
|