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