Catch only "not found" with IFNA
PremiumE-109 is a new hire who isn't in the list yet. In E2, look up the department for D2 with VLOOKUP and wrap it in IFNA to show "Not listed" instead of #N/A.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee ID | Department | Employee ID | Department | ||
| 2 | E-101 | Sales | E-109 | |||
| 3 | E-102 | Finance | ||||
| 4 | E-103 | IT | ||||
| 5 | E-104 | HR | ||||
| 6 | E-105 | Sales | ||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock IFERROR 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
IFNA returns the formula's result unless it is #N/A, the error a lookup gives when the value isn't there, and shows "Not listed" in that case. Unlike IFERROR it lets other errors through, so a typo like a column number of 3 in a two-column table still shows #REF! and gets noticed.
Common mistake: Reaching for IFERROR by habit. It hides every error, including the ones that mean the formula itself is wrong.
The answer
- E2
=IFNA(1VLOOKUP(D2,A2:B6,2,FALSE),2"Not listed")
In plain words: 1Look up the department for the ID in D2, and 2if it is not found (#N/A), show Not listed instead.