FILTER driven by a cell
Point the condition at an input cell and the view changes when it does.
Task:
Managers pick a status in F1 to see the matching orders. In E3, list the order numbers whose status equals whatever is in F1.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of FILTER
0/3The same formula in the other shapes it takes at work.
- Show only the invoices that are actually overdue
FILTER keeps the rows where your condition holds.
CoreIntermediate - FILTER whole rows
Return every column of the matching rows, not just one.
Intermediate - FILTER driven by a cell
Point the condition at an input cell and the view changes when it does.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Status | Show status | Packed | ||
| 2 | SO-201 | Packed | ||||
| 3 | SO-202 | Shipped | Orders | |||
| 4 | SO-203 | Packed | ||||
| 5 | SO-204 | New | ||||
| 6 | SO-205 | Shipped | ||||
| 7 | SO-206 | Packed |
What this exercise teachesMay contain the answer
Typing "Packed" into the formula makes a report that answers one question; pointing at F1 makes a tool that answers any of them. Pair the input cell with a dropdown and nobody needs to touch the formula again.