Table of Contents
INDEX MATCH with Multiple Criteria in VBA is a way to find a value within a table that meets multiple criteria. It works by using an INDEX function to return a value of a cell within a table based on the row and column numbers provided by a MATCH function. The MATCH function is used to find the row and column numbers based on the multiple criteria provided. The criteria is set up by using a combination of AND and OR functions to create an array of conditions that must be met. This results in INDEX MATCH returning a value from the table that meets all of the criteria provided.
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.