View Full Version : Vlookup Error with Negative Value Range

JOEYSCLEE

03-06-2017, 10:04 AM

Hi, there

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

Paul_Hossler

03-06-2017, 03:46 PM

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

03-07-2017, 09:15 AM

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

03-07-2017, 10:32 AM

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

03-08-2017, 09:49 AM

Thanks Paul for writing the code!!

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.