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.

**Additional Resources**

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

Excel: How to Use INDEX MATCH from Another Sheet

Excel: How to Use INDEX MATCH Across Multiple Columns

Excel: How to Use IF Function with INDEX MATCH

Hi Zach,

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

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.