Hi,

The best way to do this is by having the minimum and maximum values in 2
different columns (say A1:A10 and B1:B10). Also in a third column (say
C1:C10) enter numbers starting with 1 I.e. 1,2,3....

Now use the SUMPRODUCT() formula

=sumproduct((A1:A10<=9923)*(B1:B10=9923),C1;C10)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"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