Function Problems

How to Return the Maximum Value for Each Account Number in Excel

Camila Milosovich Sep 25, 2026 871 views

Question details

The user needs to find the largest value associated with specific account numbers in a dataset, outputting that max value on its corresponding row and zero on all other rows.

How to Return the Maximum Value for Each Account Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering and analyzing account data where multiple rows share the same account number, but only the row with the peak value should display it.
Observed behavior: Column C needs to evaluate if the value in Column B is the maximum for the account in Column A, returning the value if true, or zero if false.

Before you start

Ensure that your data in columns A (account numbers) and B (values) are properly formatted as numbers or clean text without extra trailing spaces, as formatting inconsistencies can cause formula errors.

Solution 1Recommended

Use the LET and MAXIFS functions

Combining the LET and MAXIFS functions provides a clean and highly efficient way to calculate the conditional maximum and evaluate the current row against it.

The MAXIFS function determines the largest value in Column B based on the matching account number in Column A. Wrapping this in the LET function allows you to define this maximum value as a variable, improving formula calculation speed and readability.

Select the target cell

Click on cell C1 (or the first cell in your target column adjacent to your data).

Enter the combination formula

Type the following formula exactly: =LET(m,MAXIFS($B:$B,$A:$A,A1),IF(B1=m,m,0)) and press Enter. This assigns the max value to the variable 'm' and checks if B1 equals 'm'.

Apply the formula to the remaining rows

Click the small square at the bottom-right corner of cell C1 (the fill handle) and drag it down to the end of your data list to apply the calculation to all accounts.

Formula Output: Rows containing the maximum value for their respective account number will display that value in Column C, while all other rows will cleanly output a 0.

Solution 2

Use standard IF and MAXIFS functions (For older versions)

If you are using an older version of Excel that does not support the LET function, you can achieve the exact same result using a standard IF function.

Easily Analyze Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logic formulas and array functions like MAXIFS, IF, and LET. It allows you to process large datasets quickly and accurately without needing to purchase expensive software.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open your document containing the account numbers and values.
2. Apply the MAXIFS logic: Select the empty cell in Column C, paste your formula =LET(m,MAXIFS($B:$B,$A:$A,A1),IF(B1=m,m,0)), and press Enter.
3. Drag to fill: Use the fill handle in WPS Spreadsheet to drag the formula down to instantly process the remaining account numbers.

Seamlessly compatible with Microsoft Excel (.xlsx and .xls) file formatsNatively supports advanced functions including MAXIFS, LET, and XLOOKUPLightweight, fast execution even when analyzing thousands of rowsFree to download and provides a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

What happens if there are duplicate maximum values for the same account?

If an account has the same maximum value on multiple rows, the provided formula will output that maximum value on all rows where the tie occurs. If you only want the first occurrence to show the value and the rest to show 0, you would need to add a tie-breaking logic based on the row position using the ROW() function.

Why is my MAXIFS formula returning a #NAME? error?

A #NAME? error typically occurs if your software version does not support the functions being used. The LET function is available in newer versions of Excel and WPS Spreadsheet. If you see this error, try using the standard IF and MAXIFS alternative formula instead.

Can I use this formula to compare text categories instead of account numbers?

Yes. The MAXIFS function evaluates the criteria range regardless of whether it contains text strings, dates, or numbers. As long as the values in Column B are numerical, you can use text categories in Column A.

How do I return a blank cell instead of a zero?

To return an empty cell instead of the number 0, simply change the last argument in the formula from 0 to an empty string (""). For example: =LET(m,MAXIFS($B:$B,$A:$A,A1),IF(B1=m,m,"")).

Excel & Data Problems