Return Multiple Matching Rows for Each Task in Excel
Question details
Return every matching source row for each task in a requested list, preferably using a dynamic-array formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A list of requested tasks must be matched against source data where each task can have multiple rows.
- Observed behavior
- The desired result is a stacked set of matching rows without helper columns or manually created unique IDs.
Place the source data in an Excel table with the task names in its first column, and put the requested tasks in a separate one-column table. Check that your Excel version supports REDUCE, LAMBDA, FILTER, TAKE, and VSTACK.
Stack all matching rows with REDUCE and FILTER
Use this dynamic-array formula to collect every matching row for each requested task in list order.
This example assumes the source table is named Table1 and the requested-task table is named TaskList, with a column named Task. Excel tables expand as rows are added.
The formula skips tasks with no matches. Repeated tasks in the request list repeat their matching rows in the output.
Go to Formulas > Name Manager > New and create Data referring to =Table1[#Data], Header referring to =Table1[#Headers], and Tasks referring to =TaskList[Task]. Replace the table and column names if yours differ.
Select an empty cell outside the tables and enter =REDUCE(Header,Tasks,LAMBDA(a,v,IF(SUM(--(TAKE(Data,,1)=v))=0,a,VSTACK(a,FILTER(Data,TAKE(Data,,1)=v))))).
Press Enter and leave the spill area below and to the right empty. The result starts with the source headers, followed by matching rows for each task.

Find matching task rows with WPS Spreadsheet
Use the task-column filter to display all rows for selected tasks, then copy the visible results. This provides a manual alternative rather than an automatically updating stacked formula.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and select a cell in the source data.
- 2. Enable filtering: On the Data tab, choose Filter to add filter arrows to the column headers.
- 3. Select the tasks: Open the task-column filter arrow, select the requested task values, and apply the filter to display their matching rows.
- 4. Copy the results: Select and copy the displayed rows, then paste them into a separate sheet or range. Reapply the filter and copy again when the task list or source data changes.

Frequently Asked Questions
What happens when a requested task has no matching rows?
The REDUCE formula skips that task. If none of the requested tasks match, it returns only the source headers.
Will new source rows and requested tasks be included automatically?
Yes, if they are added to the Excel tables referenced by Data and Tasks. Check the table names and references under Formulas > Name Manager.
Why does the formula show #SPILL!?
The result needs empty cells below and to the right of the formula. Clear the blocked cells or move the formula to an empty area outside an Excel table.
Can I use the formula if the task column is not first?
Change TAKE(Data,,1) in both places to select the correct task column, for example CHOOSECOLS(Data,2) for the second column.




