# 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:

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.

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

May 13, 2024
April 25, 2024
April 19, 2024

## 3 Replies to “Google Sheets: Use INDEX MATCH with Multiple Criteria”

1. person says:

cool formula, but it only seems to work if wrapped in an array

2. Matt Santangelo says:

Thank you for this. What would the formula look like if I wanted all of the data in a row to be copied to another tab within the same sheet based on criteria such as forward and All-Star?

3. Abhay says:

hi Zach, I attempted this but the challenge is when the combination does not exist, meaning if the MATCH returns a #N/A, the INDEX defaults to the 0th cell in the reference. So instead of #N/A or #ERROR as I would get in EXCEL, Google Sheets gives out an incorrect value. Do you have any suggestions?