Table of Contents
Google Sheets INDEX MATCH with Multiple Criteria is a powerful formula that can be used to look up and retrieve data from multiple columns in a range of data. This formula requires the user to provide criteria in multiple columns and it will return the corresponding data from the designated column. It is useful when dealing with complex data sets that have multiple criteria to be considered for retrieval. This formula can save time and make data look ups much easier.
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:
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 following tutorials explain how to perform other common operations in Google Sheets: