Excel IFERROR Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 37 of 105 · Full Excel course

The Excel IFERROR function evaluates a formula and, if it produces any error (#N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! or #NULL!), returns a replacement value you choose instead. Otherwise it returns the formula’s normal result. IFERROR in Excel keeps reports clean by turning errors into blanks, zeros or messages.

Table of Contents

IFERROR syntax

=IFERROR(value, value_if_error)

Arguments

Argument	Required	Meaning
value	Required	The formula or reference to test, for example A2/B2 or a VLOOKUP.
value_if_error	Required	What to return when value is an error: text in quotes, a number, “” for a blank, or another formula.

IFERROR is available in Excel 2007 and later, including Excel 365 and Excel for the web.

Step-by-step example

You divide sales in A2 by units in B2. When B2 is blank or zero, =A2/B2 shows #DIV/0!. Wrap it:

=IFERROR(A2/B2, "Check units")

Excel calculates A2/B2 first.
If the result is a valid number, IFERROR returns it unchanged.
If the result is any error, IFERROR returns “Check units” instead.

Practical use cases

1. Friendly message for lookups

=IFERROR(VLOOKUP(D2, $A$2:$B$500, 2, FALSE), "Not found")

The most common use: replaces #N/A when the lookup value is missing.

2. Return zero so totals still work

=IFERROR(A2/B2, 0)

A text message would break a SUM below; 0 keeps the column numeric.

3. Fallback to a second lookup

=IFERROR(VLOOKUP(D2, Table1, 2, FALSE), VLOOKUP(D2, Table2, 2, FALSE))

Tries the first table, then the second. Nest again for a third source.

4. Blank cell instead of an error

=IFERROR(INDEX(C:C, MATCH(F2, A:A, 0)), "")

5. Average that survives empty ranges

=IFERROR(AVERAGE(B2:B50), "No data")

Common mistakes and errors

Hiding real problems – IFERROR masks every error type, including typos in range names (#NAME?). Add it only after the core formula works.
Using IFERROR when only #N/A matters – IFNA catches only #N/A, so genuine #REF! or #VALUE! errors still show and get fixed.
Text result breaks SUM or AVERAGE – returning “Not found” in a numeric column makes AVERAGE skip the cell and SUMPRODUCT return #VALUE!. Return 0 or “” when the column feeds calculations.
Missing second argument – IFERROR needs both arguments; IFERROR(A2/B2) is invalid.
Old Excel – Excel 2003 does not have IFERROR. Use IF(ISERROR(x), alt, x) for compatibility.

Tips and best practices

Add IFERROR last. Build and test the core formula first so you see every error, then wrap it once the logic is right.
Match the fallback to the column type: 0 for amounts, “” for text and dates, a short message only in columns that no other formula reads.
Prefer IFNA around lookups. It hides only “not found” and lets typos and broken ranges surface.
Audit hidden errors periodically with a copy of the sheet where IFERROR is removed via Find and Replace, or by using Formulas > Error Checking.
Performance: IFERROR evaluates the formula once, so it is faster than the old IF(ISERROR(x), alt, x) pattern on large sheets.

Related functions

IFNA – traps only #N/A, the safer choice for lookups.
ISERROR and ISERR – return TRUE/FALSE tests you can use inside IF.
ERROR.TYPE – identifies which error occurred.
VLOOKUP and HLOOKUP – the functions most often wrapped in IFERROR.
Browse the Excel Formulas hub or watch tutorials on YouTube.com/@PKAnExcelExpert.

Frequently asked questions

Which errors does IFERROR catch?

All of them: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. If you want to trap only #N/A, use IFNA instead.

What is the difference between IFERROR and IFNA?

IFERROR replaces every error type. IFNA replaces only #N/A, so other errors remain visible and can be corrected. IFNA is generally better for lookup formulas.