Array Functions
Intermediate
Stop a filter returning an error when nothing matches
FILTER errors on an empty result unless you give it a fallback.
Task:
This dashboard cell shows suppliers on hold. Today there are none, and the formula is showing an error to your director. In E2, use FILTER to list suppliers from A2:A6 where the status in B2:B6 is "Hold", falling back to the text None when nothing matches.
Learning Objectives:
- Use the if_empty argument
- Compare text inside a filter
- Design for the empty case
Hints
Solve without hints for +5 XP
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 | |
|---|---|---|---|---|---|
| 1 | Supplier | Status | On hold | ||
| 2 | Alders Ltd | Active | |||
| 3 | Brant Co | Active | |||
| 4 | Corvo SA | Active | |||
| 5 | Delta Group | Active | |||
| 6 | Ekko BV | Active |
What this exercise teaches (contains the answer)
An empty filter is a normal state, not a failure. Handling it is the difference between a dashboard that survives a quiet week and one that shows #CALC! to the person who signs off your work.