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.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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.