Lookup between list of Mins & Maxs

J

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
 
R

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top