# Excel: How to Use INDEX MATCH to Return Max Value

You can use the following syntax in Excel with the INDEX and MATCH functions to find the max value in a range and return a corresponding value:

```=INDEX(B2:B11,MATCH(MAX(A2:A11),A2:A11,0))
```

This particular formula uses MAX to find the max value in the range A2:A11 and then uses INDEX and MATCH to look up the value in the range B2:B11 that corresponds to this max value.

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

## Example: Use INDEX MATCH to Return Max Value in Excel

Suppose we have the following dataset that contains information about points scored by various basketball players:

Suppose we would like to look up the max value in the Points column and return the corresponding name from the Team column.

We can type the following formula into cell D2 to do so:

`=INDEX(B2:B11,MATCH(MAX(A2:A11),A2:A11,0))`

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

This formula uses the MAX function to find the max value of 40 in the points column.

Then, the formula uses the INDEX and MATCH function to return the team name Rockets, which is the team that corresponds to the max value in the points column.

## Bonus: Find the Max Value Associated with a Lookup Value

If you instead wanted to find the max value associated with a specific lookup value, you could use the MAXIFS function instead.

For example, you could use the following formula to find the max value associated with the “Warriors” team:

`=MAXIFS(B2:B9, A2:A9, "Warriors")`

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

The formula returns a value of 32, which is the max value associated with the “Warriors” in this dataset.

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

## 2 Replies to “Excel: How to Use INDEX MATCH to Return Max Value”

1. Manuela says:

Hi Zach,
That’s super helpful. Wondering how to have ” ” if all values are zero?

1. James Carmichael says:

Hi Manuela…To use `INDEX MATCH` to return the maximum value and display an empty string if all values are zero, you can combine `INDEX`, `MATCH`, `MAX`, and `IF` functions in Excel. Here’s a step-by-step guide:

1. **Find the maximum value in the range:**
Use the `MAX` function to find the maximum value in the range.
“`excel
=MAX(A1:A10)
“`

2. **Check if all values are zero:**
Use the `IF` function to check if the maximum value is zero. If it is, return an empty string `””`.
“`excel
=IF(MAX(A1:A10)=0, “”, MAX(A1:A10))
“`

3. **Find the position of the maximum value:**
Use the `MATCH` function to find the position of the maximum value in the range.
“`excel
=MATCH(MAX(A1:A10), A1:A10, 0)
“`

4. **Return the value using INDEX MATCH:**
Combine `INDEX` and `MATCH` to return the value from the range based on the position found.
“`excel
=INDEX(A1:A10, MATCH(MAX(A1:A10), A1:A10, 0))
“`

5. **Combine all steps:**
Nest these functions together to check for zero values and return the appropriate result.
“`excel
=IF(MAX(A1:A10)=0, “”, INDEX(A1:A10, MATCH(MAX(A1:A10), A1:A10, 0)))
“`

### Example
Assume your data range is A1:A10. Here is the complete formula:
“`excel
=IF(MAX(A1:A10)=0, “”, INDEX(A1:A10, MATCH(MAX(A1:A10), A1:A10, 0)))
“`

### Explanation
1. `MAX(A1:A10)` finds the maximum value in the range A1:A10.
2. `IF(MAX(A1:A10)=0, “”, …)` checks if the maximum value is zero. If it is, it returns an empty string `””`.
3. `MATCH(MAX(A1:A10), A1:A10, 0)` finds the position of the maximum value in the range.
4. `INDEX(A1:A10, MATCH(MAX(A1:A10), A1:A10, 0))` returns the value from the range A1:A10 based on the position found.

This formula ensures that if all values in the range A1:A10 are zero, it returns an empty string. Otherwise, it returns the maximum value.