Function Problems

How to List All Names with Duplicate Scores in Excel

Khadija Khan Oct 21, 2026 868 views

Question details

The user needs to extract and list all names that share a specific duplicate value, such as a tied highest score, in an Excel tier list.

How to List All Names with Duplicate Scores in Excel

Product: Excel
Device & OS: not provided
Scenario: Attempting to retrieve multiple records that match the exact same criteria (like a tied score) from a dataset.
Observed behavior: Standard lookup functions like INDEX and MATCH only return the first matching name they encounter, failing to display the subsequent names that share the identical score.

Before you start

Ensure you are using a modern spreadsheet version (like Microsoft 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions, as older versions do not include the required FILTER function.

Solution 1Recommended

Use the FILTER Function to Extract Multiple Matches

The FILTER function is the most efficient way to bypass the single-result limitation of traditional lookups, allowing you to return an array of all names that meet your criteria.

Unlike INDEX and MATCH, which stop searching after finding the first match, the FILTER function evaluates the entire array and extracts every row that meets your logical test. Because it is a dynamic array function, it automatically spills the multiple results into the adjacent empty cells below.

Select the starting output cell

Click on the empty cell where you want your list of matching names to begin. Make sure there is plenty of empty space below this cell, as the results will dynamically expand downwards.

Enter the FILTER and MAX formula

To find all individuals with the highest score, type `=FILTER(A2:A11, B2:B11=MAX(B2:B11))` into the formula bar. In this example, A2:A11 represents the column containing the names, and B2:B11 represents the column containing the scores.

Apply and review the spilled results

Press Enter on your keyboard. The formula will instantly evaluate the data and list every name associated with the top score. If there are four people tied for the highest score, the formula will populate four rows automatically.

Targeting specific scores: You can easily modify this formula to find any specific score. Simply replace the MAX function with a target value or a cell reference, for example: `=FILTER(A2:A11, B2:B11=D2)`.

Advanced Data Analysis in WPS

Easily Extract Tied Scores and Duplicates with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like FILTER, making it incredibly simple to extract multiple names, manage tier lists, and analyze tied scores without complex formula workarounds.

1. Open your data file: Launch WPS Office and open your spreadsheet containing the list of names and scores.
2. Apply the FILTER function: Select a blank cell with enough empty space below it, and input the formula `=FILTER(A2:A11, B2:B11=MAX(B2:B11))`.
3. Analyze results instantly: Press Enter to execute the formula. WPS Spreadsheet will automatically spill all matching names into the cells below.

Fully compatible with Microsoft Excel formulas, dynamic arrays, and .xlsx file formats.Native support for advanced functions including FILTER, UNIQUE, and SORT to solve complex data lookup issues.Lightweight, feature-rich, and completely free to use for your daily data processing needs.

microsoft office alternative - wps office

Frequently Asked Questions

Why do INDEX and MATCH only return one name for duplicate scores?

Standard lookup functions like VLOOKUP, XLOOKUP, or INDEX/MATCH are designed to return the first exact match they encounter. They process data linearly from top to bottom and stop searching the array once the first matching score is found, preventing them from returning subsequent duplicates.

What does the #SPILL! error mean when using the FILTER function?

A #SPILL! error occurs when the FILTER formula tries to output an array of multiple names, but one or more cells in the required output path already contain data. To fix this, simply clear or delete the data in the blocking cells so the formula has enough blank space to expand.

How can I extract duplicate names if I don't have the FILTER function?

In older versions of Excel that lack dynamic arrays, you must use an advanced array formula combining INDEX, SMALL, IF, and ROW, and finalize it by pressing Ctrl+Shift+Enter. Because this is highly complex and prone to errors, upgrading to a modern suite like WPS Office or Microsoft 365 is highly recommended.

Can I use FILTER with multiple criteria to find names?

Yes. You can multiply logical expressions within the FILTER function to add more conditions. For example, `=FILTER(A2:A11, (B2:B11=85)*(C2:C11="Class A"))` will list all names that scored exactly 85 and are also in Class A.

Excel & Data Problems