FILTER returns the rows that satisfy a condition. Use the status in E2 to filter the source table A2:C4.
Visual walkthrough
Before
Three source rows. E2 contains In progress.
After
E5:G6 contains HS01 and HS03.
Change E2 to Done. Only HS02 should remain.
Try it here
Change the selection to see the result and matching data rows.
=FILTER(A2:C4,C2:C4=E2,"No records")| Code | Task | Status |
|---|---|---|
| HS01 | Submit documents | In progress |
| HS02 | Send minutes | Done |
| HS03 | Reconcile | In progress |
Sample data
Download sample CSV| Code (A) | Task (B) | Status (C) |
|---|---|---|
| HS01 | Submit documents | In progress |
| HS02 | Send minutes | Done |
| HS03 | Reconcile | In progress |
If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.
Step-by-step instructions
- Enter the sample task table in A1:C4.
- Type In progress in E2 and enter the formula below in E5.
=FILTER(A2:C4,C2:C4=E2,"No records") - Keep E5:G6 clear so the results can spill into adjacent cells. HS01 and HS03 should appear.
- Change E2 to Done to show only HS02. A status with no matches should show No records.
Frequently asked questions
What does #SPILL! mean?
Excel cannot place the array results. Check for existing values, merged cells or another obstruction in the expected output area.
Reference: Microsoft Support