How can I use INDEX MATCH to return the maximum value in Excel?

By /

INDEX MATCH is a powerful function in Excel that allows users to search for specific data within a range and return a corresponding value. In order to use INDEX MATCH to return the maximum value in Excel, the MAX function can be nested within the MATCH function to find the row number of the maximum value. This row number can then be used in the INDEX function to return the corresponding value from a specific column. This approach offers a more flexible and efficient way to find the maximum value compared to traditional methods such as sorting or using the MAX function alone. By utilizing the combination of INDEX MATCH and MAX function, users can easily identify and extract the maximum value from a given data set in Excel.

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

Excel INDEX MATCH return max value

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:

x

Related terms:

  1. How Can INDEX MATCH Be Used to Return the Maximum Value in Excel?
  2. How can VLOOKUP be used to return the maximum value in Google Sheets?
  3. Excel Formula: If Match then Return Yes or No
  4. How to Match Two Columns and Return a Third in Excel
  5. Does the Google Sheets formula return Yes or No if there is a match?
  6. How to Use SUM with INDEX MATCH in Excel?
  7. How to use INDEX MATCH Across Multiple Columns in Excel?
  8. How can INDEX MATCH be used from another sheet in Excel?
  9. How can I select the row with the maximum value by group in MySQL?
  10. What is the MySQL query to select the row with the maximum value in a specific column?