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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Check if String Contains Another String
VBA: How to Count Number of Rows in Range
VBA: How to Write COUNTIF and COUNTIFS Functions