Lookup between list of Mins & Maxs

[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

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

=INDEX(C$2:C$100,MATCH(SUMPRODUCT(A$2:A$100*(A$2:A$100<=D3)*(B$2:B$100>=D3)),A$2:A$100,1))

Rick

 

[JLGWhiz]

This is untested, but should work.

Sub minmax()
lstRw = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lstRw
If Range("D3").Value > Cells(i, 1).Value _
And Range("D3").Value < Cells(i, 2).Value Then
Range("E3") = Cells(i, 3).Value
End If
Next
End Sub

 

[J.W. Aldridge]

Thanx All!

Both worked purfectly!