I don't know where my formula is wrong, because I still get the wrong result.
Please see below, where the data is from cells A1:D10, "ARC" in cell A15 and
9923 in cell A16.

Minimum Maximum
ARC 0 10,000 Result 1
ARC 4,000 10,000 Result 2
ARC 10,000 9,999,999 Result 3
ARD 0 10,000 Result 4
ARD 10,000 9,999,999 Result 5
DCC 0 15,000 Result 6
DCC 4,000 9,999,999 Result 7
DCC 15,000 Result 8
DCD 0 9,999,999 Result 9


ARC
9923 Result 4

=INDIRECT(ADDRESS(SUMPRODUCT(--($A$2:$A$10=A15),--($B$2:$B$10<=A16),
--($C$2:$C$10=A16),ROW($A$2:$A$10)),4))




"Roger Govier" wrote:

Hi Christine

Tom's formula returns the correct result for me.
The only way I can see it returning a value 2 rows below the value required,
is if your data starts in Row 4, not in row 2 as Tom assumed.
Change $2 to $4 throughout the formula, and see what that does.
--
Regards
Roger Govier

"Christine" wrote in message
...
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