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.
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.