A message when FILTER finds nothing
PremiumIn E4, list the reps whose sales reached the target in F1. Nobody did this month, so give FILTER a third argument to show "No one" instead of an error.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Target | 600 | ||
| 2 | Ann | North | 420 | ||||
| 3 | Ben | South | 310 | Reps on target | |||
| 4 | Cy | North | 150 | ||||
| 5 | Di | West | 500 | ||||
| 6 | Ed | South | 260 | ||||
| 7 | |||||||
| 8 |
Premium lesson
Unlock FILTER and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
No one sold 600 or more, so the test is FALSE in every row. Without a third argument FILTER returns #CALC!; with one it shows that text instead. Lower the target to 400 and the formula lists Ann and Di.
Common mistake: Leaving out the if_empty argument. The formula is right, but an empty result shows #CALC! instead of a clear message.
The answer
- E4
=FILTER(1A2:A6,2C2:C6>=F1,3"No one")
In plain words: Return 1the reps in A2:A6 2whose sales in C2:C6 reached the target in F1, or 3No one if nobody did.