How to Return All Matching Rows in WPS Spreadsheets Instead of the First XLOOKUP Result
Question details
The user needs to return every matching row from a second table when a lookup identifier appears multiple times, replacing a single XLOOKUP result with a dynamic array formula.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a lookup identifier that appears multiple times in a second table and wants all corresponding records, such as all department name and list price rows for one Atta3 ID.
- Observed behavior
- XLOOKUP returns only the first matching result, so the user does not see the additional matching rows.
Ensure your WPS Spreadsheets version supports dynamic array functions such as XLOOKUP, FILTER, and UNIQUE. Open the workbook and confirm that the return range and lookup column are clearly identified, ideally using Excel table references.
Return all matching rows with UNIQUE and FILTER
Use this method when you want every matching row from the source table, optionally removing any duplicates that may appear.
XLOOKUP is designed to return a single matching value, so it is not the right tool when a lookup value appears more than once. The FILTER function returns all rows that meet a condition, and wrapping it in UNIQUE removes duplicate rows from the spill range.
Click the cell where you want the dynamic array results to begin, typically in the row beside the first lookup identifier.
Type =UNIQUE(FILTER(Table6[[DeptName]:[ListPrice]],Table6[Atta3 ID]=$B10,"No matches")) and adjust Table6, the return range, and $B10 to match your workbook.
Press Enter. WPS Spreadsheets fills the matching rows automatically; if no match exists, the cell displays “No matches”.
Return selected columns from all matching rows with CHOOSECOLS
Use this method when you only need specific columns from the matching rows, such as department name and list price, rather than the entire table range.
Use WPS Spreadsheets to Return All Matching Rows with FILTER and UNIQUE
WPS Office Spreadsheets can directly complete this task. Use a dynamic array formula with FILTER and UNIQUE to return every matching row in .xlsx workbooks without complex VBA or manual filtering.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx file that contains the lookup table and source table.
- 2. Confirm dynamic array support: Make sure you are using a version of WPS Spreadsheets that supports XLOOKUP, FILTER, and UNIQUE; if needed, update WPS Office from the official download page.
- 3. Click the destination cell: Click the cell where you want the matching rows to spill, next to the lookup identifier.
- 4. Enter the dynamic array formula: Type =UNIQUE(FILTER(Table6[[DeptName]:[ListPrice]],Table6[Atta3 ID]=$B10,"No matches")) and replace Table6 and $B10 with your own table and cell references.
- 5. Press Enter to return all matching rows: Press Enter. WPS Spreadsheets returns every matching row and automatically spills the results into the surrounding cells.
Frequently Asked Questions
Why does XLOOKUP only return the first match when multiple rows have the same ID?
XLOOKUP is a lookup function that stops as soon as it finds the first matching value. To return every row that satisfies the condition, use FILTER, optionally wrapped in UNIQUE.
Can WPS Spreadsheets use the FILTER and UNIQUE dynamic array formulas?
Yes. WPS Spreadsheets supports dynamic array functions including FILTER, UNIQUE, XLOOKUP, and CHOOSECOLS. Use them in a cell and the results spill into adjacent cells automatically.
How do I return only selected columns from all matching rows?
Wrap the FILTER result in CHOOSECOLS or INDEX. For example, =CHOOSECOLS(FILTER(Table6,Table6[Atta3 ID]=$B10,"No matches"),1,3) returns only the columns at the specified positions.
What can I add to the FILTER formula to avoid duplicates?
Wrap FILTER in UNIQUE, such as =UNIQUE(FILTER(return_range, condition, "No matches")). This keeps only unique rows from the matching results.
What should I do if my version of WPS Spreadsheets does not recognize FILTER?
Update WPS Office to the latest version. Dynamic array functions require a current version; after updating, reopen the workbook and try the formula again.
