vlookup closest value

[chungtinhlak]

Please refer to picture, i have a score card based on percentage. My actual percentage is not on the score card but I just wnat the closest match using vlookup = true to get the score.

My highest percentage is 115%, my actual is 150%, but when i use vlookup, it keeps on giving me the score for 102% not the score for 115%.

please help.

[mdmackillop]

A UDF solution? =Closest(E7,A2:A14,1)


Function Closest(Tgt As Range, Data As Range, oset As Long)
Dim Test, Cel As Range
Test = Tgt
For Each Cel In Data
If Abs(Tgt - Cel) < Test Then
Test = Abs(Tgt - Cel)
Closest = Cel.Offset(, oset)
End If
Next
End Function

[chungtinhlak]

this function goes onto a module or i have to do an addin ?

[Zack Barresse]

Sort in ascending order. This should give you the equal or next lowest value equivalent to your value. This will not, however, differentiate between two values such as 102% and 118% where your value is 117%, as the 102% would still be returned. Or are you looking for a mean value equivalent?

Your question is a little confusing as 118% is your highest in the picture, not 115% as you stated. Perhaps a couple of examples, along with the expected results?

[chungtinhlak]

Oh i'm sorry, score for 118% should be the one returning.

[Zack Barresse]

And you tried the sorting???