Learn How to Find the Highest Value and the Related Row in a Pivot Table in Excel Formula with Three Different Approaches and Detailed Examples

A pivot table is a powerful tool that allows you to summarize and analyze data from a large data set. You can use a pivot table to group, sort, filter, and calculate data in various ways. One of the common tasks that you may want to perform with a pivot table is to find the maximum value in a certain field and return the corresponding row that contains that value. For example, you may want to find out which salesperson has the highest sales in a certain region, or which product has the highest profit margin in a certain category.

There are different ways to achieve this goal, depending on your preference and the structure of your data. In this article, we will show you three methods that you can use to return the row that contains the highest value in a pivot table in Excel formula. We will also explain the basic theory behind each method and provide a detailed example with real numbers.

Method 1: Using the MAX and MATCH functions

The first method that we will show you is to use the MAX and MATCH functions in combination. The MAX function returns the maximum value in a range of cells, and the MATCH function returns the relative position of a value in a range of cells. By using these two functions together, you can find the position of the maximum value in a pivot table and then use that position to return the corresponding row.

The basic formula for this method is:

=INDEX (row_range,MATCH (MAX (value_range),value_range,0))

where:

• row_range is the range of cells that contains the rows that you want to return. For example, if you want to return the names of the salespeople, you can use the range that contains their names.
• value_range is the range of cells that contains the values that you want to find the maximum of. For example, if you want to find the highest sales, you can use the range that contains the sales amounts.
• 0 is the match type argument that tells the MATCH function to look for an exact match.

The formula works by first finding the maximum value in the value_range using the MAX function. Then, it uses the MATCH function to find the position of that value in the same range. Finally, it uses the INDEX function to return the value in the row_range at that position.

Example

Suppose you have the following pivot table that shows the sales of different products in different regions:

If you want to find out which product has the highest sales in the East region, you can use the following formula:

=INDEX (A2:A5,MATCH (MAX (C2:C5),C2:C5,0))

The formula will return D, which is the product that has the highest sales in the East region.

Method 2: Using the MAX and VLOOKUP functions

The second method that we will show you is to use the MAX and VLOOKUP functions in combination. The VLOOKUP function looks up a value in the first column of a table and returns a value in the same row from another column. By using this function with the MAX function, you can find the maximum value in a pivot table and then return the corresponding row from another column.

The basic formula for this method is:

=VLOOKUP (MAX (value_range),table_array,col_index_num,FALSE)

where:

• value_range is the range of cells that contains the values that you want to find the maximum of. For example, if you want to find the highest sales, you can use the range that contains the sales amounts.
• table_array is the range of cells that contains the table that you want to look up from. For example, if you want to look up the names of the products, you can use the range that contains the product names and the sales amounts.
• col_index_num is the column number in the table that you want to return the value from. For example, if you want to return the product names, you can use the column number that contains the product names.
• FALSE is the range_lookup argument that tells the VLOOKUP function to look for an exact match.

The formula works by first finding the maximum value in the value_range using the MAX function. Then, it uses the VLOOKUP function to look up that value in the first column of the table_array and return the value in the same row from the col_index_num column.

Example

Suppose you have the same pivot table as in the previous example. If you want to find out which product has the highest sales in the West region, you can use the following formula:

=VLOOKUP (MAX (D2:D5),A2:D5,1,FALSE)

The formula will return D, which is the product that has the highest sales in the West region.

Method 3: Using the MAXIFS and XLOOKUP functions

The third method that we will show you is to use the MAXIFS and XLOOKUP functions in combination. The MAXIFS function returns the maximum value in a range of cells that meets one or more criteria, and the XLOOKUP function looks up a value in a range of cells and returns a value in the same position from another range of cells. By using these two functions together, you can find the maximum value in a pivot table that meets a certain condition and then return the corresponding row from another range.

The basic formula for this method is:

=XLOOKUP (MAXIFS (value_range,criteria_range,criteria),value_range,row_range)

where:

• value_range is the range of cells that contains the values that you want to find the maximum of. For example, if you want to find the highest sales, you can use the range that contains the sales amounts.
• criteria_range is the range of cells that contains the criteria that you want to apply. For example, if you want to find the highest sales in a certain region, you can use the range that contains the region names.
• criteria is the value that you want to match in the criteria_range. For example, if you want to find the highest sales in the North region, you can use the value “North”.
• row_range is the range of cells that contains the rows that you want to return. For example, if you want to return the names of the products, you can use the range that contains the product names.

The formula works by first finding the maximum value in the value_range that meets the criteria in the criteria_range using the MAXIFS function. Then, it uses the XLOOKUP function to look up that value in the value_range and return the value in the same position from the row_range.

Example

Suppose you have the same pivot table as in the previous examples. If you want to find out which product has the highest sales in the South region, you can use the following formula:

=XLOOKUP (MAXIFS (B2:B5,B1:B5,"South"),B2:B5,A2:A5)

The formula will return D, which is the product that has the highest sales in the South region.