Finding a value to the left Reverse Vlookup using INDEX and MATCH VBA

I've seen many forum saying that it is possible to find data to the left column, as opposed to Vlookup function.

I have a sheet of which I would like to get the value from the left. In other words, I would like to seach for a serial # on the right, say column 3, and get a value on column 1.

What would an alternative to Vlookup in VBA? (i.e., reverse vlookup)

I seen several forums with examples using index match, but I cannot understand the syntax. Can someone please provide me with a simple example?

Here is what I was using before and found out that Vlookup cannot search to the left:

Sal = Application.WorksheetFunction.VLookup("3491709101",Sheets(PreviousTabName).Range(ThisRAnge), 2, False)

4 answers

  • answered 2019-03-13 18:43 Mistella

    I would use an Index-Match combination. Something like this should work:

    Sal = Application.WorksheetFunction.INDEX(Sheets(PreviousTabName).Range(ThisRAnge),Application.WorksheetFunction.MATCH("3491709101",Application.WorksheetFunction.INDEX(Sheets(PreviousTabName).Range(ThisRAnge),0,3),0),1)
    

    Alternatively, assuming column 1's reference is stored in variable 1stColumn, and column 3 in 3rdColumn then the code should be:

    Sal = Application.WorksheetFunction.INDEX(1stColumn,Application.WorksheetFunction.MATCH("3491709101",3rdColumn,0))
    

  • answered 2019-03-13 19:22 user11198948

    I would like to seach [sic] for a serial # on the right, say column 3, and get a value on column 1.

    Use Application.Match to retrieve the row number. Using Application.Index is sort of overkill since Cells will do just as well.

    dim n as variant, val as variant
    
    n = application.match("3491709101", Sheets(PreviousTabName).Range("C:C"), 0)
    
    if not iserror(n) then
        val = Sheets(PreviousTabName).Cells(n, "A").Value
        debug.print val
    end if
    

  • answered 2019-03-13 19:27 Shai Rado

    I would replace the VLOOKUP with Application.Match, but you need to add an error handler scenario, just in case the Application.Match will fail.

    Code

    Option Explicit
    
    Sub UseMatch()
    
    Dim PreviousTabName As String
    Dim SourceSht As Worksheet
    Dim ThisRange As Range
    Dim LastRow As Long, MatchRow As Variant
    Dim Val
    
    ' set the worksheet object where you have the data you are trying to match it with
    Set SourceSht = ThisWorkbook.Sheets("Sheet2") ' <-- modify "Sheet2" to your sheet's name
    
    ' set the range where you want to search for a Match
    With SourceSht
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row ' find last row in column "C" , you can change to the column you need
    
        ' set dynamic Range
        Set ThisRange = .Range(.Cells(1, "C"), .Cells(LastRow, "C")) ' change the column to where your data you are trying to compare lies
    End With
    
    If Not IsError(Application.Match("3491709101", ThisRange, 0)) Then
        MatchRow = Application.Match("3491709101", ThisRange, 0) ' row of match found
        Val = SourceSht.Range("A" & MatchRow) ' getting the value 2 columns to the left of column "C"
        MsgBox Val
    Else
        ' Match error, raise a msgbox
        MsgBox "Unable finding 3491709101 in range " & ThisRange.Address(0, 0, xlA1), vbCritical, "Error"
    End If
    
    End Sub
    

    Note: when dealing with Numeric values, you need to make sure they are formatted the same in both places.

    For instance, you are looking for "3491709101" which is a numeric value converted to String, so if the source data in ThisRange is not set-up the same (as String) match will fail.

  • answered 2019-03-13 20:18 GMalc

    Looking at your comment to @Mistella, I don't think that you need Vlookup or Match. My understanding is that you want to find the serial number "3491709101" in Column 8 and set the value from Columns(2) to the Variable Sal. If I'm right, then a simple Find is what you need.

    Dim rng As Range, findVal As Variant, Sal As Variant
    Dim PreviousTabname As Worksheet, ThisRange As Range
    
    Set rng = Sheets(PreviousTabname).Range(ThisRange)
    
        Set findVal = rng.Find(What:="3491709101", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If findVal Is Nothing Then
                Exit Sub
            Else: Sal = findVal.Offset(, -6).Value
    
            End If
        MsgBox Sal