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
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)
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
3rdColumnthen the code should be:
Sal = Application.WorksheetFunction.INDEX(1stColumn,Application.WorksheetFunction.MATCH("3491709101",3rdColumn,0))
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
I would replace the
Application.Match, but you need to add an error handler scenario, just in case the
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
ThisRangeis not set-up the same (as String) match will fail.
Looking at your comment to @Mistella, I don't think that you need
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