IFERROR vs IFNA: which to use, and when not to hide errors
IFERROR hides every error; IFNA hides only #N/A. Why the difference matters for lookups, which one to choose, and the errors you should leave visible.
Use IFNA around a lookup. It hides only #N/A, the "not found"
result, so every real mistake still shows.
Use IFERROR only when every possible error has the same meaning. That is less often than people think.
What each one catches
Both functions have the same shape: the formula to try, then what to show if it fails.
=IFNA(value, value_if_na)=IFERROR(value, value_if_error)
The difference is which errors reach the second argument.
| Error | What it usually means | IFNA | IFERROR |
|---|---|---|---|
| #N/A | A lookup found no match | Catches | Catches |
| #REF! | The formula points at a cell that is not there | Shows it | Catches |
| #VALUE! | Text where a number should be | Shows it | Catches |
| #DIV/0! | A division by zero or an empty cell | Shows it | Catches |
| #NAME? | A function name with a typo | Shows it | Catches |
Look at the second column. Only the first row is a fact about your data. The others are usually a mistake in the formula or in a cell. Those are the errors you want to see.
The same lookup, both ways
Here is a price list in A2:B6 and some order codes in column D. Every example below uses it.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Price list | |||
| 2 | Code | Price | Order | |
| 3 | P100 | 4.50 | P200 | |
| 4 | P200 | 12.00 | P900 | |
| 5 | P300 | 7.25 | P300 | |
| 6 | P400 | 3.00 |
P900 is not on the list. Both wrappers give the same answer for it, and both are right.
=IFNA(VLOOKUP(D4,A3:B6,2,FALSE),"Not listed") gives Not listed.
=IFERROR(VLOOKUP(D4,A3:B6,2,FALSE),"Not listed") gives Not
listed too.
Now somebody types 3 as the column number by mistake. The range A3:B6 has only 2 columns. P200 is on the list, so the right answer is 12.
=IFERROR(VLOOKUP(D3,A3:B6,3,FALSE),"Not listed") gives Not
listed. That is false. P200 is on the list.
=IFNA(VLOOKUP(D3,A3:B6,3,FALSE),"Not listed") gives
#REF!. That is true. The formula is broken, and now you can see
it.
Copy the IFERROR version down 500 rows and every row says Not listed. The sheet looks correct, so the mistake stays. For more on this error, read what #REF! means.
IFNA is not a cure
IFNA cannot tell you why a lookup found nothing. Look at D5. It holds P300 with a space on the end. You cannot see the space, but VLOOKUP can.
=IFNA(VLOOKUP(D5,A3:B6,2,FALSE),"Not listed") gives Not listed.
But P300 is on the list.
=IFNA(VLOOKUP(TRIM(D5),A3:B6,2,FALSE),"Not listed") gives 7.25.
TRIM removes the space first.
A number stored as text does the same thing. So before you add IFNA, check that the missing values are really missing. The #N/A article lists every cause and a test for each one.
Wrap the smallest part
Where you put the wrapper matters as much as which one you choose. Say F3 holds a quantity of 3 and F4 holds the text three.
=IFNA(VLOOKUP(D3,A3:B6,2,FALSE),0)*F3 gives 36. The wrapper
covers only the lookup.
=IFNA(VLOOKUP(D3,A3:B6,2,FALSE),0)*F4 gives #VALUE!. The
quantity is text, and you can see that.
=IFERROR(VLOOKUP(D3,A3:B6,2,FALSE)*F4,0) gives 0. The wrapper
covers the whole formula, so the bad quantity is hidden.
Choose the fallback with care too. A price of 0 looks like a free item. On an invoice, words like Not listed are safer than a number.
When IFERROR is the right choice
IFERROR is fine when every error means the same thing. Turning text into a number is a good
example. =IFERROR(VALUE(H2),"Not a number") gives 12 when H2
holds the text 12. It gives Not a number when H2 holds 12a.
Even then, ask one question first. If a different error appeared here, would you want to know? If the answer is yes, use IF or IFNA instead.
For a division, test the divisor with IF. =IFNA(1/0,0) still shows
#DIV/0!, because IFNA ignores it.
=IFERROR(1/0,0) gives 0.
XLOOKUP has its own IFNA
XLOOKUP takes a fourth argument for the not-found case.
=XLOOKUP(D4,A3:A6,B3:B6,"Not listed") gives Not listed. It works
like IFNA: only a missing match uses the fourth argument.
So with XLOOKUP you often need no wrapper at all. XLOOKUP vs VLOOKUP compares the two functions.
Questions people ask
I see IF(ISNA(...)) in an old file. Is that wrong?
No, it works. =IF(ISNA(VLOOKUP(D4,A3:B6,2,FALSE)),"Not listed",VLOOKUP(D4,A3:B6,2,FALSE)) gives Not listed. But it writes the lookup twice. IFNA does the same job with one lookup.
How do I mark errors without hiding them?
Use an IS function in a separate column. If E4 holds the lookup,
=IF(ISNA(E4),"Add to list","") gives Add to list. E4 still shows
#N/A, so nothing is hidden.
Does every version of Excel have IFNA?
IFNA came in Excel 2013. IFERROR is older. So Excel 2013 and every later version have both. That includes Microsoft 365.