Google Sheets: Use INDEX MATCH with Multiple Criteria


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

=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteria3)*...,0))

where:

  • reference: The range from which a value will be returned
  • MATCH: Gives the position of your search key
  • 1: Specifies a fixed search key
  • criteria1, criteria2, criteria3: The criteria to match
  • 0: Species to search for an exact value

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

Example: INDEX MATCH with Multiple Criteria in Google Sheets

Suppose we have the following data in Google Sheets that shows information for various basketball players:

Now suppose we would like to find the Points value associated with the player who is on the Mavs, has a Position of Forward, and has an All Star Status of Yes.

We can use the following INDEX MATCH formula to find this Points value:

=INDEX(D:D,MATCH(1,(A:A=A15)*(B:B=B15)*(C:C=C15),0))

The following screenshot shows how to use this formula in practice:

INDEX MATCH with multiple criteria in Google Sheets

The formula correctly returns a Points value of 11 for the player who is on the Mavs team, has a position of Forward, and has an All Star Status of Yes.

Note that if we change the values in row 15, our INDEX MATCH formula will automatically update.

For example, suppose we instead use this formula to find the Points value for the player who is on the Spurs team, has a position of Guard, and has an All Star Status of Yes:

The formula correctly returns a Points value of 22 for this player.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

How to Perform a Reverse VLOOKUP in Google Sheets
How to Use a Case Sensitive VLOOKUP in Google Sheets

Leave a Reply

Your email address will not be published.