VBA: How to Use INDEX MATCH

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