Return All Duplicate Score Records in Excel Instead of the First XLOOKUP Match
Question details
The user needs to return every Excel record that has the same score rather than only the first matching row returned by XLOOKUP.

- Product
- Microsoft Excel lookup formulas
- Device & OS
- not provided
- Scenario
- Multiple rows in a worksheet share a Subcomp Score, and the user wants to display all matching records or sort all records by score.
- Observed behavior
- XLOOKUP returns only the first row that matches a duplicate score.
Confirm that the score column and the selected score cell use the same data type, such as numbers stored as numbers. Make sure the output area below and beside the formula is empty so the dynamic array can spill all matching rows.
Use FILTER to return every row with the selected score
Use this method when you need all records whose score matches a selected value. FILTER preserves duplicate matches instead of stopping at the first result.
XLOOKUP is designed to return one matching value or record, typically the first match. FILTER returns every row that meets the condition and spills the results into adjacent cells.
Click the blank cell where the matching records should begin, such as a cell on a results sheet.
Type =FILTER('Download sheet'!A4:E101,'Download sheet'!N4:N101=S37,"No match") and press Enter. Replace S37 with the cell containing the score you want to find if needed.
Excel fills the matching rows from Download sheet!A4:E101 automatically. If a #SPILL! error appears, clear cells that block the output range.

Sort selected record columns by Subcomp Score
Use this formula when you want to display all records in score order rather than filter for one selected score.
Return Every Matching Score Record with FILTER in WPS Spreadsheets
WPS Spreadsheets supports dynamic array formulas such as FILTER, SORT, and CHOOSECOLS, so you can return all duplicate score records in one formula instead of relying on a single XLOOKUP result.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the score data.
- 2. Select a blank results cell: Click the first empty cell where the matching rows should be displayed.
- 3. Enter a FILTER formula: Enter =FILTER('Download sheet'!A4:E101,'Download sheet'!N4:N101=S37,"No match") and press Enter.
- 4. Allow the results to spill: Keep the cells around the formula empty so WPS Spreadsheets can display every matching record.

Frequently Asked Questions
Why does XLOOKUP return only one duplicate score record?
XLOOKUP is intended to return one result for a lookup value, and it normally returns the first matching record. Use FILTER when you need every matching row.
How do I return all rows with the score in cell S37?
Enter =FILTER('Download sheet'!A4:E101,'Download sheet'!N4:N101=S37,"No match") in a blank cell. The formula returns all rows from A4:E101 whose column N value equals S37.
What does the "No match" text do in a FILTER formula?
It is the optional third FILTER argument. Excel displays "No match" when no rows meet the score condition instead of showing a calculation error.
How can I sort records by Subcomp Score without filtering one score?
Use =SORT(CHOOSECOLS('Download sheet'!$A$4:$E$101,1,5,4,2),2,1). It selects the specified columns and sorts the resulting table by its second column in ascending order.
Why do I get a #SPILL! error after entering FILTER?
A #SPILL! error means one or more cells in the required output range are not empty. Clear or move the blocking cells, then recalculate the formula.




