Function Problems

Find the Highest Test Score for Each User in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Return the highest test score associated with a selected user name from a table containing repeated names.

Find the Highest Test Score for Each User in Excel
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.
Before you start

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.

Solution 1Recommended

Use MAXIFS to find a selected user's highest score

Use this formula when your version of Excel supports MAXIFS.

1
Enter the user name

Type the name to look up in cell D2.

2
Enter the MAXIFS formula

Select E2 and enter =MAXIFS($B$2:$B$21,$A$2:$A$21,D2), then press Enter.

3
Repeat for other users

Enter additional names below D2, then drag the fill handle on E2 down to calculate each user's highest score.

Use MAXIFS to find a selected user's highest score
Check for missing names: MAXIFS returns 0 when no rows match. To display a message instead, use =IF(COUNTIF($A$2:$A$21,D2)=0,"Not found",MAXIFS($B$2:$B$21,$A$2:$A$21,D2)).
WPS Spreadsheets

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. 1. Open the score table: Open WPS Spreadsheets and select Open to load your Excel workbook.
  2. 2. Enter a user name: Type the name to look up in D2, with names in A2:A21 and scores in B2:B21.
  3. 3. Calculate the highest score: Select E2, enter =MAXIFS($B$2:$B$21,$A$2:$A$21,D2), and press Enter.
  4. 4. Calculate more results: Add names below D2 and drag the fill handle from E2 down to apply the formula to each name.
Calculate the maximum score for each user with a formula.Open and save Microsoft Excel XLSX files.Work with familiar spreadsheet cells and formulas.Use WPS Office as a free, lightweight spreadsheet option.
microsoft office alternative - wps office

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.