How to Return the Max Value for Multiple Rows with the Same Unique Identifier in Excel

Sometimes, you may have a data set that contains multiple rows with the same unique identifier, such as a product ID, a customer ID, or a transaction ID. In such cases, you may want to return the maximum value for each unique identifier, based on some criteria, such as the highest price, the latest date, or the largest quantity.

For example, suppose you have a table that shows the sales data of different products in different regions. Each product has a unique ID, but there may be multiple rows for the same product ID, as the same product may be sold in different regions, or on different dates, or at different prices. You may want to find out the maximum price for each product ID, regardless of the region or date.

There are different ways to achieve this task in Excel, such as using pivot tables, array formulas, or the MAXIFS function. In this article, we will explain how to use the MAXIFS function, which is available in Excel 2019 and later versions, or in Excel for Microsoft 365.

The MAXIFS Function

The MAXIFS function returns the maximum value among cells that meet one or more criteria. The syntax of the MAXIFS function is:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

where:

• max_range is the range of cells that contains the values that you want to find the maximum of.
• criteria_range1 is the range of cells that contains the first criteria that you want to apply.
• criteria1 is the first criteria that you want to apply. It can be a value, a cell reference, or an expression.
• criteria_range2, criteria2, etc. are optional arguments that specify additional criteria and ranges that you want to apply. You can have up to 126 pairs of criteria and ranges.

The MAXIFS function works by comparing each cell in the max_range with the corresponding cells in the criteria_ranges, and returning the maximum value among those that match all the criteria. If no cells match the criteria, the function returns #N/A.

Example

Let’s see how to use the MAXIFS function with an example. Suppose you have the following table in Excel, which shows the sales data of different products in different regions.

You want to find out the maximum price for each product ID, regardless of the region or date. To do this, you can use the MAXIFS function as follows:

• In cell F2, enter the formula =MAXIFS(D:D,A:A,E2), where D:D is the max_range, A:A is the criteria_range1, and E2 is the criteria1.
• Copy the formula down to cell F4.

The result is:

The formula in cell F2 returns 120, which is the maximum price for product A, as it matches the criteria that the product ID is A. Similarly, the formula in cell F3 returns 80, which is the maximum price for product B, and the formula in cell F4 returns 70, which is the maximum price for product C.

Other Approaches

As mentioned earlier, there are other ways to return the max value for multiple rows with the same unique identifier in Excel, such as using pivot tables or array formulas. Here are some brief explanations of these approaches:

• Pivot tables: A pivot table is a tool that allows you to summarize and analyze data in a tabular format. You can use a pivot table to group the data by the unique identifier, and then apply the MAX function to the values that you want to find the maximum of. For example, you can create a pivot table from the sales data table, and drag the Product ID field to the Rows area, and the Price field to the Values area. Then, right-click on any cell in the Price column, and select Summarize Values By > Max. This will show the maximum price for each product ID in the pivot table.
• Array formulas: An array formula is a formula that performs calculations on multiple values at once, and returns an array of results. You can use an array formula to return the max value for multiple rows with the same unique identifier, by using the MAX and IF functions together. For example, you can enter the following formula in cell F2, and press Ctrl + Shift + Enter to confirm it as an array formula:

=MAX(IF(A:A=E2,D:D))

This formula returns 120, which is the maximum price for product A, as it evaluates the IF function for each cell in the range A:A, and returns an array of prices that match the criteria that the product ID is A. Then, it applies the MAX function to this array, and returns the maximum value. You can copy the formula down to cell F4 to get the results for the other product IDs.