Logical
How to Use IFNA in Excel (Catch Only #N/A Errors)
IFNA catches the #N/A error specifically and returns a value of your choice instead, while letting every other error type still surface. It is the precise tool for cleaning up lookups that miss.
That precision is why IFNA is often safer than the broader IFERROR: a genuine #VALUE! or #REF! bug still shows so you can fix it. This guide covers the syntax, lookup examples, and how IFNA differs from IFERROR.
IFNA syntax
=IFNA(value, value_if_na) | Argument | Required | What it does |
|---|---|---|
value | Required | The formula to evaluate, typically a lookup like VLOOKUP or XLOOKUP. |
value_if_na | Required | What to return if value produces #N/A: text in "quotes", a number, or "" for a blank cell. |
Note: IFNA traps ONLY #N/A. Other errors (#VALUE!, #REF!, #DIV/0!) pass straight through. Use IFERROR to catch every error type. Available in Excel 2013 and later, and in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Friendly message for a failed VLOOKUP
=IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found") If the lookup value is missing, VLOOKUP returns #N/A and IFNA swaps in "Not found". A genuine error like #REF! would still show, unlike with IFERROR.
2. Return a blank on a miss
=IFNA(XLOOKUP(E2, A:A, C:C), "") Two double quotes with nothing between them return an empty string, so a missing match simply shows a blank cell.
3. MATCH that may not find a value
=IFNA(MATCH(E2, A2:A100, 0), 0) MATCH returns #N/A when the value is absent; IFNA replaces it with 0 so a downstream INDEX or calculation keeps working.
4. Keep real errors visible
=IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "Missing") Because IFNA ignores other errors, a #REF! from a deleted column or a #VALUE! still appears, which is exactly what you want while building a sheet.
How to write IFNA step by step
- 1
Build and test your lookup first so you know it returns the right value on a match.
- 2
Wrap it: type =IFNA( before the formula.
- 3
After the formula, type a comma.
- 4
Enter what to show on #N/A ("Not found", 0, or "" for blank).
- 5
Close the bracket ) and press Enter, then fill the formula down as needed.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Other errors still appear | IFNA only handles #N/A, so a #VALUE!, #REF!, or #DIV/0! is left untouched. | That is by design. If you want to catch every error type, use IFERROR instead. |
Returns 0 unexpectedly | value_if_na was left empty, which Excel treats as 0. | Use "" (two quotes) if you want a blank cell rather than 0. |
#NAME? | IFNA is not available in Excel 2010 or earlier. | Use IFERROR, or the older =IF(ISNA(...), ...) pattern. |
Too few arguments | The comma between value and value_if_na is missing. | Make sure there are exactly two arguments separated by a comma. |
Frequently asked questions
What is the difference between IFNA and IFERROR?
IFNA catches only the #N/A error; IFERROR catches every error type (#N/A, #VALUE!, #REF!, #DIV/0!, and more). IFNA is safer for lookups because real bugs still surface instead of being hidden.
When should I use IFNA instead of IFERROR?
Use IFNA on lookups (VLOOKUP, XLOOKUP, MATCH) where the only expected error is a missing value. It hides the "not found" #N/A while still letting genuine errors show so you can fix them.
Can I use IFNA with VLOOKUP?
Yes, it is the ideal pairing: =IFNA(VLOOKUP(...), "Not found") replaces the #N/A from a missing match without masking other problems with the formula.
Does IFNA work in Google Sheets?
Yes. IFNA works the same in Google Sheets and in Excel 2013 and later. Older Excel versions need IFERROR or IF(ISNA(...)).