Multiple Min & Max range lookup.

[J.W. Aldridge]

I have a list of Mins, and a list of Max's. If someone was to enter in
a number in D3, I would like for it to lookup the entire list of Mins
and Maxs to see if the number entered falls within range of a Min-Max
row.

Example, since 1801 is between 1800 and 2200, it returns the
equivalent word or number in row C.

If I were to put 550 in D3, it would return APPLES.
If I were to put 1499 in D3, it would return CHERRIES.


A B C D E
MIN MAX
500 1000 APPLES
1200 1500 CHERRIES 1801 PLUMS
1800 2200 PLUMS

Note: I have a long list of mins and maxs.

Thanx

 

[RagDyeR]

Try this *array* formula:

=INDEX(C2:C100,MATCH(1,(D3>=A2:A100)*(D3<=B2:B100),0))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have a list of Mins, and a list of Max's. If someone was to enter in
a number in D3, I would like for it to lookup the entire list of Mins
and Maxs to see if the number entered falls within range of a Min-Max
row.

Example, since 1801 is between 1800 and 2200, it returns the
equivalent word or number in row C.

If I were to put 550 in D3, it would return APPLES.
If I were to put 1499 in D3, it would return CHERRIES.


A B C D E
MIN MAX
500 1000 APPLES
1200 1500 CHERRIES 1801 PLUMS
1800 2200 PLUMS

Note: I have a long list of mins and maxs.

Thanx

 

[J.W. Aldridge]

Worked PUUUUUR-FECTLY!!!

Thanx a million! Better yet, when I earn my first million, you got a
Krystal on me (that's with cheese!).

 

[RagDyeR]

You're welcome, and thanks for the feed-back.

BUT ... what's a Krystal?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Worked PUUUUUR-FECTLY!!!

Thanx a million! Better yet, when I earn my first million, you got a
Krystal on me (that's with cheese!).