Array FunctionsIntermediate

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.

Interactive Spreadsheet

Read about FILTER

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of FILTER

0/2

The same formula in the other shapes it takes at work.

  1. Stop a filter returning an error when nothing matches

    FILTER errors on an empty result unless you give it a fallback.

    CoreIntermediate
  2. FILTER with a threshold cell and a fallback

    When the limit is set high enough, nothing matches — and the message has to say so.

    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.

6 rows × 5 columns1 cell you fill in
ABCDE
1SupplierStatusOn hold
2Alders LtdActive
3Brant CoActive
4Corvo SAActive
5Delta GroupActive
6Ekko BVActive
What this exercise teachesMay contain 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.