Backwards Vlookup

[Jonmo1]

Have you ever wanted to do a backwards Vlookup, to find a value in say column G, and return the corrosponding value from column D ?

Well, the thought just occured to me today that I can make a UDF to do just that....

Public Function LeftLookup(Myval As Variant, Myrange As Range, Col As Long)
lookupcolumn = Myrange.Column + Myrange.Columns.Count - 1
lookupstartrow = Myrange.Row
lookupendrow = Myrange.Row + Myrange.Rows.Count - 1
resultcol = 0 - Col + 1
result = "Not Found"
For i = lookupstartrow To lookupendrow
If Cells(i, lookupcolumn).Value = Myval Then
    result = Cells(i, lookupcolumn).Offset(0, resultcol)
    GoTo valuefound
End If
Next i
valuefound:
If result = "" Then result = "Blank"
LeftLookup = result
End Function

Almost exactly the same as Vlookup...But reading Right to Left. Plus, you don't have to put in the True/False argument....honestly, how often do you use "True" in a Vlookup??? I can probably work that in there, if anybody actually wants it.

so there are 3 arguments in the LEFTLOOKUP function..

exampe formula using LEFTLOOKUP

=LEFTLOOKUP(A1,E:M,9)

A1 is the value to find in column M
E:M is the Range to look in, (again, it looks for A1 in column M)
Returns the Value in the 9th column to the LEFT from M (in this case E)

looks for exact matches, and does not need to be sorted. But if there are dupes, it will return the TOP value.

I added a couple twists...
instead of returning #N/A when the match is not found, it actually returns "Not Found"
And returns "Blank" instead of 0 when the resulting cell is blank.

Hope this helps


-------

and for those that don't know what a UDF is and how to use it...It's called a User Defined Function. To install a UDF, right click the excel icon in top left corner next to file...view code
click insert - module
paste code in that white window.

Now you can use the function like any other formula in excel.

 

[Tom Urtis]

Have you ever wanted to do a backwards Vlookup, to find a value in say column G, and return the corrosponding value from column D ?

Interesting UDF, thanks for posting it, but I'm curious, what advantage if any do you see by using a UDF over a faster native formula, especially this UDF that has a Variant type and looping ?

If I understand the issue you raise per your opening question, usually when I need to do a reverse lookup, this native formula might suffice without a UDF, assuming the data range E1:M50 to find what's in column E for what row in column M there exists the value in A1 - -
INDEX(E1:M50,MATCH(A1,M1:M50,0),1)

to find what's in F
INDEX(E1:M50,MATCH(A1,M1:M50,0),2)
and so on.

To trap for the absence of the value in A1 being in column M - -
=IF(ISNA(INDEX(E1:M50,MATCH(A1,M1:M50,0),1)),A1&" not found",INDEX(E1:M50,MATCH(A1,M1:M50,0),1))
though there are probably more efficient ways to write that than I did.


It's late in SF as I write this so apologies if I misunderstand the premise, just curious about the UDF's applicability.

 

[Jonmo1]

Thans for the feedback Tom, the advantage this UDF has over native functions is just for the simplicity of it. While I agree and understand why native Functions are better than UDFs, some people don't really understand the Index/Match functions. So This is much easier to write and understand.

I've gotten to know Vlookup inside and out, so just reversing vlookup will be like second nature. I'm just barely understanding Index/Match formulas. I had to sit and think a minute to figure out how your formula works.

Sure Native functions are faster and less expensive, but if you don't use thousands of UDFs in your sheet, big deal.

Also, what's wrong with using Variant?

 

[Jonmo1]

Though your comments helped me find a way to do it without a loop. I've also made the column reference optional (left collumn of given range is default). And modified so it works across sheets.

Public Function LeftLookup(Myval As Variant, Myrange As Range, Optional Col As Integer)
If Col = 0 Then Col = Myrange.Columns.Count
    On Error GoTo Handler
Dim sht As Worksheet
    Set sht = Sheets(Myrange.Worksheet.Name)
Dim lookupcolumn, lookupstartrow, lookupendrow, resultcol, resultrow As Integer
    lookupcolumn = Myrange.Column + Myrange.Columns.Count - 1
    lookupstartrow = Myrange.Row
    lookupendrow = Myrange.Row + Myrange.Rows.Count - 1
    resultcol = 0 - Col + 1
    resultrow = WorksheetFunction.Match(Myval, sht.Range(sht.Cells(lookupstartrow, lookupcolumn), _
        sht.Cells(lookupendrow, lookupcolumn)), 0) + (Myrange.Row - 1)
    result = sht.Cells(resultrow, lookupcolumn).Offset(0, resultcol).Value
If result = "" Then result = "Blank"
LeftLookup = result
GoTo good
Handler:
LeftLookup = "Not Found"
good:
End Function