Array Functions
Intermediate

FILTER with a threshold cell and a fallback

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

Task:

Finance flags expenses at or above the limit in F1. This month nothing reaches it. In E3, list the claims at or above the limit, showing None over limit when there are none.

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.

    Core
    Intermediate
  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 × 6 columns1 cell you fill in
ABCDEF
1ClaimAmountLimit2500
2C-1420
3C-21890Over limit
4C-3760
5C-42499
6C-5310
What this exercise teachesMay contain the answer

C-4 is one short of the limit, so nothing matches and FILTER would return #CALC! without a fallback. With the message in place the dashboard reads correctly this month, and lists claims the moment the limit is lowered or a big claim arrives.