Home › Excel functions › IFERROR

IFERROR in Excel: Syntax, Examples and When Not to Use It

IFERROR returns the result of a formula, unless that formula produces an error, in which case it returns a value you choose instead. It's the quickest way to turn a sheet full of #N/A and #DIV/0! into something readable.

Excel CSE · 3 min read

IFERROR syntax

IFERROR catches every error type: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!.

Example: hide #DIV/0!

You want the average price per unit, but one product hasn't sold anything yet.

Column D uses =B2/C2. Banana has 0 units, and dividing by zero gives #DIV/0!. Column E wraps the same formula:

Now Banana shows 0, and the other rows still show their normal result. Use "" instead of 0 to leave the cell empty.

Example: a friendly message for VLOOKUP

When VLOOKUP can't find a value it returns #N/A. Wrap it to show a message instead:

If the product in E2 exists, you get its price; if not, you get Not found. With XLOOKUP you don't need IFERROR for this: it has a built-in if_not_found argument.

IFNA: only catch #N/A

IFNA works like IFERROR but only catches #N/A. Other errors still show.

For lookups this is usually the better choice. "Not found" is an expected situation, but a #REF! or #NAME? means something is broken in your formula, and you want to see that.

When not to use IFERROR

IFERROR hides every error, including real mistakes. A typo in a function name, a deleted column or a wrong range all disappear behind your friendly message, and you might never notice the numbers are wrong.

• Get the formula working first, then add IFERROR.
• Prefer IFNA for lookups, so other errors stay visible.
• Don't return 0 when an error should be looked into: a zero looks like a real result and flows into totals and averages.
• For division, an explicit check is clearer: =IF(C2=0, "", B2/C2).

Not sure what an error means? See Excel errors explained.