Function Problems

Return All Duplicate Score Records in Excel Instead of the First XLOOKUP Match

John WilsonJohn Wilson Nov 12, 2026 869 views

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.

Return All Duplicate Score Records in Excel Instead of the First XLOOKUP Match
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the first output cell

Click the blank cell where the matching records should begin, such as a cell on a results sheet.

2
Enter the FILTER formula

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.

3
Review the spilled results

Excel fills the matching rows from Download sheet!A4:E101 automatically. If a #SPILL! error appears, clear cells that block the output range.

Use FILTER to return every row with the selected score
Duplicate records are retained: Every row whose value in column N equals the score in S37 is returned, including rows with identical scores.
Work with duplicate lookup results in WPS Spreadsheets

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. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the score data.
  2. 2. Select a blank results cell: Click the first empty cell where the matching rows should be displayed.
  3. 3. Enter a FILTER formula: Enter =FILTER('Download sheet'!A4:E101,'Download sheet'!N4:N101=S37,"No match") and press Enter.
  4. 4. Allow the results to spill: Keep the cells around the formula empty so WPS Spreadsheets can display every matching record.
Use FILTER to list every row that matches a selected score.Sort score records dynamically with SORT and CHOOSECOLS.Open and edit Microsoft Excel .xlsx and .xls workbooks with format compatibility.Use a familiar spreadsheet interface for formulas, tables, and analysis.
microsoft office alternative - wps office

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.