Reverse Vlookup

[khodjo]

I am doing a reverse vlookup on a table. The object is to find a value from the table and return the corresponding number in the first column. I used a code I found online:

Function RL(ModK As Range, LookupTable As Range)
HRow = LookupTable.Rows(1).Row - 1
HCol = LookupTable.Columns(1).Column - 1

RL = ""

For Each cell In LookupTable
If cell.Value = ModK.Value Then
RL = RL & Cells(cell.Row, 1).Value
End If
Next cell


End Function

It works great except that the Modk value that is passed to the function must match a value in the LookupTable otherwise I get an error or blank.

My question is : Is there a way to return the closest match of a value being passed from the LookupTable ?


Thanks

 

[Lewiy]

You can do a reverse lookup using INDEX and MATCH, for example:
=INDEX(A:A,MATCH(C1,B:B))
Where you are finding C1 in column B and returning value from column A. By not specifying the “match type” in the MATCH function, you get a closest match as with VLOOKUP.

 

[khodjo]

You can do a reverse lookup using INDEX and MATCH, for example:
=INDEX(A:A,MATCH(C1,B:B))
Where you are finding C1 in column B and returning value from column A. By not specifying the “match type” in the MATCH function, you get a closest match as with VLOOKUP.

Here's what I mean
0 0.25 0.5 0.75
0.01 0.0005 0.0005 0.0005 0.0005
0.02 0.0014 0.0014 0.0015 0.0015
0.03 0.0028 0.0028 0.0028 0.0029
0.04 0.0044 0.0045 0.0046 0.0046
0.05 0.0064 0.0065 0.0066 0.0067
0.06 0.0085 0.0087 0.0089 0.0090
0.07 0.0109 0.0112 0.0114 0.0116
0.08 0.0135 0.0139 0.0142 0.0145
0.09 0.0162 0.0168 0.0172 0.0176


I am trying to enter the table with a calculated value of 0.0167 , which is approx 0.0168 and return 0.09 as the answer