VBA: How to Use INDEX MATCH with Multiple Criteria

You can use the following basic syntax to perform an INDEX MATCH with multiple criteria in VBA:

Sub IndexMatchMultiple()
    Range("F3").Value = WorksheetFunction.Index(Range("C2:C10"), _
    WorksheetFunction.Match(Range("F1"), Range("A2:A10"), 0) + _
    WorksheetFunction.Match(Range("F2"), Range("B2:B10"), 0) - 1)
End Sub

This particular example looks up the value in cell F1 within the range A2:A10 and the value in cell F2 within the range B2:B10 and returns the corresponding value in the range C2:C10 to cell F3.

The following example shows how to use this syntax in practice.

Example: Perform INDEX MATCH with Multiple Criteria Using VBA

Suppose we have the following dataset in Excel that contains information about basketball players:

Suppose we would like to look up the player that matches the team name in cell F1 and the position in cell F2 and return the name in cell F3.

We can create the following macro to do so:

Sub IndexMatchMultiple()
    Range("F3").Value = WorksheetFunction.Index(Range("C2:C10"), _
    WorksheetFunction.Match(Range("F1"), Range("A2:A10"), 0) + _
    WorksheetFunction.Match(Range("F2"), Range("B2:B10"), 0) - 1)
End Sub

When we run this macro, we receive the following output:

The macro looks up “Spurs” in the Team column and “Forward” in the Position column and correctly returns the name “Eric” in cell F3.

If we change the values in cells F1 and F2 and run the macro again, it will be able to find the player name based on the new values.

For example, suppose we change the team name to “Mavs” and position to “Center” and run the macro again:

The macro looks up “Mavs” in the Team column and “Center” in the Position column and correctly returns the name “Chad” in cell F3.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Use INDEX MATCH
VBA: How to Check if String Contains Another String
VBA: How to Count Number of Rows in Range