IFERROR vs IFNA: Which Should You Use?

The short answer to IFERROR vs IFNA: use IFNA around lookups, because it replaces only the #N/A "not found" error, and use IFERROR when you expect other harmless errors, such as dividing by zero. IFERROR hides every error, including the ones that point to a real mistake. This guide is for anyone who cleans up lookup columns or reports and wants friendly messages without hiding problems.

IFERROR vs IFNA at a glance#

The two functions work the same way. The only difference is which errors they catch.

How the syntax differs#

Both take two parts: the formula to try, and what to show if it fails.

=IFERROR(value, value_if_error)
=IFNA(value, value_if_na)

• value is the formula you want, such as a VLOOKUP or a division.
• value_if_error or value_if_na is what to show instead: a message in double quotes like "Not found", a number like 0, or "" for an empty-looking cell.

If the formula works, both functions just pass its result through. The IFERROR function guide has more examples.

The same lookup handled both ways#

Here is an order list with codes in column A and a price list in D1:F5. Code P-999 has been discontinued, so it isn't in the price list.

Task 1: a code that isn't in the list#

=VLOOKUP(A4,$D$2:$F$5,2,FALSE) returns #N/A, because P-999 isn't there. Both functions can replace it:

=IFERROR(VLOOKUP(A4,$D$2:$F$5,2,FALSE),"Not found")
=IFNA(VLOOKUP(A4,$D$2:$F$5,2,FALSE),"Not found")

Both show Not found. For the other codes, both show the item name, such as Stapler for P-101. So far there is no difference.

Task 2: a mistake in the formula#

Now say someone types column 4 instead of 2. The table D:F has only three columns, so the VLOOKUP returns #REF! for every row.

=IFERROR(VLOOKUP(A2,$D$2:$F$5,4,FALSE),"Not found")
=IFNA(VLOOKUP(A2,$D$2:$F$5,4,FALSE),"Not found")

The IFERROR version shows Not found for P-101, even though P-101 is in the list. Every row says the same, and nothing warns you. The IFNA version shows #REF!, which tells you straight away that the formula is broken. This is the main reason to prefer IFNA for lookups.

Task 3: a misspelled function#

Typos in function names are just as easy to miss. Here VLOOKUP is missing its O:

=IFERROR(VLOKUP(A2,$D$2:$F$5,2,FALSE),"Not found")
=IFNA(VLOKUP(A2,$D$2:$F$5,2,FALSE),"Not found")

Excel doesn't know VLOKUP, so the inner formula returns #NAME?. IFERROR turns that into Not found on every row, which looks like a data problem instead of a typo. IFNA lets #NAME? through, so you spot the spelling at once.

Task 4: dividing by zero#

Not every error comes from a lookup. On a sales sheet where B2 holds revenue and C2 holds units sold, the price per unit is =B2/C2. If a product sold 0 units, that returns #DIV/0!. That error is expected and harmless, so IFERROR is the right tool:

=IFERROR(B2/C2,0)

This shows 0 instead of the error. =IFNA(B2/C2,0) would still show #DIV/0!, because IFNA only catches #N/A.

When to use IFERROR#

Use IFERROR when an error is a normal, expected result and any error type means "nothing to show". It fits when:

• You divide by a value that can be 0, such as growth from last year when a product is new: =IFERROR((C2-B2)/B2,"New").
• You combine several functions that can fail in different ways, such as text functions on messy imported data. For example, =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2) returns the first name from a full name, or the whole cell when there is no space, because FIND returns #VALUE! when it finds nothing.
• The file must work in Excel 2010 or older, where IFNA doesn't exist.

Always test the formula before you wrap it. Once it is inside IFERROR, a mistake looks the same as a normal "nothing here".

When to use IFNA#

Use IFNA whenever the error you expect is "not found". It fits when:

• You wrap VLOOKUP, HLOOKUP, MATCH or INDEX MATCH. A missing value gives #N/A, and IFNA replaces just that.
• The file will be edited by others. If someone breaks a reference or misspells a function, the real error still shows.
• You check whether something is on a list. =IFNA(MATCH(A4,$D$2:$D$5,0),"Not listed") returns the position of the code in the price list, or Not listed for P-999.
• You want a blank instead of #N/A. =IFNA(VLOOKUP(A4,$D$2:$F$5,2,FALSE),"") shows an empty-looking cell.

For more on where #N/A comes from, see our guide on how to fix the #N/A error in Excel.

What about XLOOKUP's "not found" argument?#

If you use XLOOKUP, you often need neither function. Its 4th argument, if_not_found, does the same job as an IFNA wrapper:

=XLOOKUP(A4,$D$2:$D$5,$E$2:$E$5,"Not found")

This shows Not found for P-999. Like IFNA, it only covers the "no match" case, so other errors still show. XLOOKUP needs Excel 2021, Excel 2024 or Microsoft 365. In older files, IFNA around VLOOKUP is the closest match.

Common mistakes with IFERROR and IFNA#

These slips cause most hidden or unhandled errors:

1. Wrapping a formula before checking it. Write the plain formula, check a few results, then add IFERROR or IFNA.
2. Using IFERROR on every lookup. It hides #REF! and #NAME?, which are real mistakes. Use IFNA for lookups.
3. Expecting IFNA to catch #DIV/0! or #VALUE!. It won't. Use IFERROR for those.
4. Showing 0 when 0 is a real value. A price of 0 looks like a real price. Use "" or a word like "Not found" instead.
5. Forgetting the quotes around text. =IFNA(VLOOKUP(...),Missing) returns #NAME?, because Excel reads Missing as a name it doesn't know. Text needs double quotes: "Missing".
6. Swapping the two parts. The formula to try goes first, the fallback goes second.
7. Hiding a fixable cause. If a code should match but doesn't, the cause is often an extra space. Fix the data with TRIM rather than covering the error.

Text formulas fail in their own ways too: for example, FIND returns #VALUE! when it can't find the text, which IFNA won't catch. Our guide to FIND vs SEARCH explains why. For combining text safely, see CONCATENATE vs CONCAT vs TEXTJOIN, and for a full walk-through of the function, see how to use IFERROR in Excel.

Our recommendation#

Use IFNA for lookups and IFERROR for calculations. IFNA replaces only "not found", which is the one error a lookup is expected to give, so any real mistake still shows up. IFERROR is the right tool when an error is normal and harmless, such as dividing by zero, but check your formula first, because it hides everything.

A quick rule helps: if the only error you expect is "not found", use IFNA. If you can name the other error you expect, such as #DIV/0!, and you know it is harmless, use IFERROR. If you can't say which error you expect, don't wrap the formula yet.

In short: IFNA around lookups, IFERROR around calculations, and never as a way to make a broken formula look fine.

Practise it#

Try the IFNA exercise on this page with the discontinued code P-999. Then practise IFERROR in a live spreadsheet on a sheet with real #DIV/0! errors, and work through fixing lookup errors, where you diagnose #N/A, #REF! and silent wrong answers.