How to Return All Matching Rows in WPS Office Spreadsheets with XLOOKUP Alternatives
Question details
The user needs to return every task table row whose task code matches a requested code, instead of only the first match returned by XLOOKUP.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a task database where multiple rows can share the same task code and the user wants all matching records for each requested code.
- Observed behavior
- XLOOKUP returns only the first match, leaving additional matching rows unreported.
Confirm that the task data and request codes are in separate WPS Spreadsheets tables with consistent headers, and that the task code column names match the formula or Power Query join fields.
Stack all matching rows with FILTER and REDUCE
Use this formula-based method when you need a dynamic spill range that automatically returns every matching task row for each requested code.
XLOOKUP normally stops at the first match. Wrapping FILTER inside REDUCE lets WPS Spreadsheets evaluate each unique requested task code separately and stack the matching rows into one dynamic array.
Select the data range in WPS Spreadsheets, go to Insert > Table, enable My table has headers, and name the tables TaskData and Requests.
Click an empty cell where the returned header and rows should appear.
Type =REDUCE(Header,UNIQUE(Requests[Requested Work TASK CODE]),LAMBDA(acc,code,LET(matches,FILTER(TaskData,TaskData[Task Code]=code,code),VSTACK(acc,EXPAND(matches,,10,"Not Found"))))). Replace Header with the actual header range or a named range.
Press Enter and confirm WPS Spreadsheets fills all matching rows below the header.
Merge tables in Power Query with a left outer join
Choose this refreshable query method when you prefer a no-formula workflow that returns every matching row and can be updated as data changes.
Return every matching row with WPS Office Spreadsheets
Use WPS Office Spreadsheets to combine FILTER, REDUCE, and Power Query so every matching task row is returned instead of only the first XLOOKUP result.
- 1. Open workbook in WPS Office Spreadsheets: Open the workbook containing the TaskData and Requests tables in WPS Office Spreadsheets.
- 2. Enter the FILTER/REDUCE formula: Click an empty cell, enter =REDUCE(Header,UNIQUE(Requests[Requested Work TASK CODE]),LAMBDA(acc,code,LET(matches,FILTER(TaskData,TaskData[Task Code]=code,code),VSTACK(acc,EXPAND(matches,,10,"Not Found"))))), and press Enter.
- 3. Review the spilled rows: Confirm all matching task rows appear under the header row.
- 4. Refresh with Power Query if needed: For a refreshable result, go to Data > From Table/Range, merge the two queries with a left outer join, expand the matching rows, and choose Close & Load.
Frequently Asked Questions
Why does XLOOKUP return only one row when multiple rows match?
XLOOKUP is designed to return the first match it finds. To return every matching row, use a dynamic array formula such as FILTER inside REDUCE, or merge the request and task tables with a Power Query left outer join.
Does the REDUCE/FILTER formula work in WPS Office Spreadsheets?
Yes. Open the workbook in WPS Office Spreadsheets, make sure the requests and task data are Excel tables, and enter the formula in an empty area. WPS Office Spreadsheets supports dynamic array functions when your version is up to date.
How do I get the formula to work when table headers are not named Header?
Replace the Header argument with the actual header range, such as TaskData[#Headers], or define a named range named Header before entering the formula.
What is the Power Query step for returning all matching rows?
Load both tables with Data > From Table/Range, use Home > Merge Queries > Merge Queries as New, set the join kind to Left Outer, and then expand the matching task columns before using Close & Load.
