How to Use IFERROR in Excel to Hide #N/A and #DIV/0! Errors

Nothing makes a spreadsheet look broken like a column full of #N/A or #DIV/0!. Often these errors are expected. A lookup didn't find a match, or a row doesn't have data yet. IFERROR lets you replace them with something tidier.

=IFERROR(your formula, what to show if it's an error)

If your formula works, you see its normal result. If it returns any error, you see the second part instead.

A lookup returns #N/A when it can't find a match. Wrap it in IFERROR:

=IFERROR(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found")

Now unmatched rows show Not found instead of #N/A.

Calculating a percentage or average where some rows are zero or blank gives #DIV/0!:

=IFERROR(B2/C2, 0)

Rows where C2 is empty now show 0.

The second part can be anything:

• "" (two double quotes) shows a blank cell. This is the most popular choice.
• 0 shows zero, which is useful if other formulas add these cells up.
• "Not found", "Check data" or any other message in quotes.
• Another formula, for example a second lookup in a different list:

=IFERROR(VLOOKUP(E2, List1, 2, FALSE), VLOOKUP(E2, List2, 2, FALSE))

IFNA works exactly like IFERROR, but only catches #N/A:

=IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found")

That's better for lookups, because #N/A just means "no match", which is expected. Other errors like #REF! or #NAME? mean the formula itself is broken, and IFNA lets you still see them. IFNA is available in Excel 2013 and later.

If you use XLOOKUP, you don't need either one. It has a built-in "if not found" argument:

=XLOOKUP(E2, A2:A100, C2:C100, "Not found")

IFERROR hides all errors, including genuine mistakes. If you mistype a function name or delete a column the formula needs, IFERROR quietly shows your blank or zero, and you may never notice the numbers are wrong.

Good habits:

• Build and test the formula first, then add IFERROR once you know it works.
• Use IFNA for lookups where possible.
• Show a message like "Check" rather than 0 if a wrong zero could mislead someone.

IFERROR still shows an error. Check your brackets. The closing bracket of your original formula must come before the comma.

Totals ignore my "Not found" cells. That's correct. Text is skipped by SUM. Use 0 instead if you want them counted as zero.

#NAME? You're on Excel 2003 or earlier, which doesn't have IFERROR, or there's a typo in the function name.

Wrap a formula in IFERROR(…, "") to swap errors for a blank. For lookups, prefer IFNA so real mistakes still stand out. And always test a formula before you hide its errors.