Table of Contents
INDEX MATCH is a combination of two Excel functions, INDEX and MATCH, designed to offer a more powerful alternative to using VLOOKUP. It can be used in VBA by referencing the worksheet containing the data to be looked up, then using the two functions to return the desired result. This method is more flexible than VLOOKUP and can be used to find the position of a value in a list, or to retrieve the value from a specific position. It is also useful for performing lookups when the data is not sorted.
You can use the following basic syntax to perform an INDEX MATCH in VBA:
Sub IndexMatch()
Dim i As Integer
'Perform index match
For i = 2 To 11
Cells(i, 5).Value = WorksheetFunction.Index(Range("A2:A11"), _
WorksheetFunction.Match(Cells(i, 4).Value, Range("B2:B11"), 0))
Next i
End Sub
This particular example looks up the values in cells 2 through 11 of the fourth column of the worksheet within the range B2:B11 and then returns the corresponding values in the range A2:A11 to the fifth column of the worksheet.
The following example shows how to use this syntax in practice.
Example: Perform INDEX MATCH Using VBA
Suppose we have the following dataset in Excel that contains information about basketball players:
For each player in column D, suppose we would like to find their team name from column A and then write the team name in column E.
We can create the following macro to do so:
Sub IndexMatch()
Dim i As Integer
'Perform index match
For i = 2 To 11
Cells(i, 5).Value = WorksheetFunction.Index(Range("A2:A11"), _
WorksheetFunction.Match(Cells(i, 4).Value, Range("B2:B11"), 0))
Next i
End Sub
When we run this macro, we receive the following output:
Notice that this macro was able to look up each player name and then return their corresponding team name in column E.
Note that within the For loop, the syntax Cells(i,5).value specifies that we would like the team names to be returned in the fifth column of the worksheet, i.e. column E.
If we change this syntax to Cells(i,6).value then the team names will be returned in the sixth column of the worksheet instead:
Sub IndexMatch()
Dim i As Integer
'Perform index match
For i = 2 To 11
Cells(i, 6).Value = WorksheetFunction.Index(Range("A2:A11"), _
WorksheetFunction.Match(Cells(i, 4).Value, Range("B2:B11"), 0))
Next i
End Sub
When we run this macro, we receive the following output:
Notice that the team names are now returned in the sixth column of the worksheet (column F) instead.