Finding a proper number.

B

bplumhoff

Hello,

If your data resides in A1:C8, then I suggest to insert my macro shown
below and then enter into cell D1
=lookup2(C1,$B$1:$B$8,$A$1:$A$8)
and copy this formula down to D5.

Please notice that with your problem definition HD20A will never be
returned.

HTH,
Bernd
------------ snip here -----------------
Option Explicit

Function lookup2(vSV As Variant, vSA As Variant, vRA As Variant) As
Variant
'Similar to lookup() but it looks up the biggest value in vSA which is
less-equal than vSV
'vSA has to be sorted, lowest first!!
'Remember that lookup() looks up the smallest value in the search-array
which is greater-equal than search-value.
Dim i As Long
i = 1
Do While i <= vSA.Count
If vSV <= vSA(i) Then
lookup2 = vRA(i)
Exit Function
End If
i = i + 1
Loop
lookup2 = "OUT OF RANGE"
End Function
----------- snip here again --------------
 
Top