Find the Highest Test Score for Each User in Excel
Question details
Return the highest test score associated with a selected user name from a table containing repeated names.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- User names are listed in column A and their test scores are listed in column B, with multiple rows per user.
- Observed behavior
- The desired result is one maximum score for the selected user, rather than a list of all their scores.
Check that user names are in column A, scores are numeric values in column B, and the selected name is in D2. Adjust the example ranges to cover the same rows in both columns.
Use MAXIFS to find a selected user's highest score
Use this formula when your version of Excel supports MAXIFS.
Type the name to look up in cell D2.
Select E2 and enter =MAXIFS($B$2:$B$21,$A$2:$A$21,D2), then press Enter.
Enter additional names below D2, then drag the fill handle on E2 down to calculate each user's highest score.

Use an array formula in older versions of Excel
Use MAX and IF when MAXIFS is not available.
Find each user's highest score in WPS Spreadsheets
Open your score table in WPS Spreadsheets and use MAXIFS to return the highest score for a selected name.
- 1. Open the score table: Open WPS Spreadsheets and select Open to load your Excel workbook.
- 2. Enter a user name: Type the name to look up in D2, with names in A2:A21 and scores in B2:B21.
- 3. Calculate the highest score: Select E2, enter =MAXIFS($B$2:$B$21,$A$2:$A$21,D2), and press Enter.
- 4. Calculate more results: Add names below D2 and drag the fill handle from E2 down to apply the formula to each name.

Frequently Asked Questions
How do I calculate the highest score for every user?
Enter each user name once in column D, enter =MAXIFS($B$2:$B$21,$A$2:$A$21,D2) in E2, and copy the formula down alongside the names.
What can I use if my Excel version does not have MAXIFS?
Enter =MAX(IF($A$2:$A$21=D2,$B$2:$B$21)) in the result cell. In older desktop versions of Excel, confirm it with Ctrl+Shift+Enter.
Why does the formula return 0 for a user?
The user's highest score may be 0, or the name may not match any row. Check with =COUNTIF($A$2:$A$21,D2) and look for spelling differences or extra spaces.
Will these formulas work with negative scores?
Yes, when the scores are numeric and the user has at least one matching row. MAXIFS and the MAX(IF(...)) array formula can return a negative maximum.




