Hide #N/A with IFERROR
Step 1 of 3
When a lookup can't find a value it shows #N/A. IFERROR catches errors: =IFERROR(value, value_if_error).
Click Continue when you're ready.
Already know it? Type the formula straight into E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Country | Code | Country | ||
| 2 | DK | Denmark | IS | |||
| 3 | SE | Sweden | ||||
| 4 | NO | Norway | ||||
| 5 | FI | Finland | ||||
| 6 | DE | Germany | ||||
| 7 | ||||||
| 8 |
Need help?
Why this works
IFERROR(value, value_if_error) returns the value when it works and the fallback when it is any error. Use it on purpose: it also hides typos such as a wrong range, which is why XLOOKUP and IFNA catch only #N/A.
Common mistake: Wrapping every formula in IFERROR out of habit. It also hides typos and wrong ranges, so use it only where an error is expected.
The answer
- E2
=IFERROR(1VLOOKUP(D2,A2:B6,2,FALSE),2"Unknown")
In plain words: 1Look up the code in D2 in the table A2:B6, and 2if that gives an error, show Unknown instead.
Wrap the lookup in IFERROR: =IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Unknown"). In Excel 365 you can also use XLOOKUP with an if_not_found value.
+20 points