Vlookup Error with Negative Value Range

[JOEYSCLEE]

Hi, there
The negative value cannot be looked up from the listed range. Could you please help to fix the formula?:help

[Paul_Hossler]

Nothing wrong with the formula, it's the data

18565

18566

Has to be sorted

You can use a combination of MATCH and INDEX if you don't want to sort it

[JOEYSCLEE]

Thanks Paul!! But, when I used combination of MATCH and INDEX function, I needed to sort those data for the result. So, could you please advise another formula that I don't need to sort those data?

=INDEX($C$2:$C$20,MATCH(E2,$A$2:$A$20,1))

[Paul_Hossler]

I was wrong - MATCH used like that does require sorted data -- sorry

Maybe some WS guru has a way to do it with formulas


A user defined function would be able to do it

18567




Option Explicit

Function LookupPercentage(DataIn As Range, ValIn As Double) As Variant
Dim r As Range
Dim i As Long
Dim v As Variant

On Error GoTo NiceExit

Set r = Intersect(DataIn, DataIn.Parent.UsedRange)

v = r.Value

If ValIn >= 0# Then
For i = LBound(v, 1) + 1 To UBound(v, 1)
If v(i, 1) <= ValIn And ValIn < v(i, 2) Then
LookupPercentage = v(i, 3)
Exit Function
End If
Next I
Else
For i = LBound(v, 1) + 1 To UBound(v, 1)
If v(i, 2) < ValIn And ValIn <= v(i, 1) Then
LookupPercentage = v(i, 3)
Exit Function
End If
Next I
End If

NiceExit:
LookupPercentage = CVErr(xlErrNum)
End Function

[JOEYSCLEE]

Thanks Paul for writing the code!!