Function Problems

Return Multiple Matching Rows for Each Task in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 868 views

Question details

Return every matching source row for each task in a requested list, preferably using a dynamic-array formula.

Return Multiple Matching Rows for Each Task in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the named ranges

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.

2
Enter the formula

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))))).

3
Check the results

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.

Stack all matching rows with REDUCE and FILTER
Formula availability: If Excel returns a #NAME? error, check that your version supports all the functions used. If it returns #SPILL!, clear any cells blocking the output.
Filter task rows in WPS Spreadsheet

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. 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and select a cell in the source data.
  2. 2. Enable filtering: On the Data tab, choose Filter to add filter arrows to the column headers.
  3. 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. 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.
Display multiple matching rows for selected tasks without helper columnsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceCopy filtered results to a separate location
microsoft office alternative - wps office

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.