How IFERROR Works in Excel: Examples and Common Uses

In Excel, IFERROR evaluates a formula and returns your chosen fallback if that formula produces an error; otherwise, it returns the formula’s result. Its basic form is =IFERROR(value, value_if_error), which can replace a visible error such as #DIV/0! with a message, blank, or other value.

What IFERROR does

Excel formulas can return error values when they cannot complete a calculation. Dividing by zero, referring to a deleted cell, or requesting a lookup result that does not exist are common examples. IFERROR provides a way to decide what the spreadsheet should display in those situations, rather than leaving the error value visible.

The function has two arguments. The first is the calculation or expression to evaluate. The second is the value Excel should return if the first argument produces an error. If the first argument succeeds, Excel returns its result and does not use the fallback as the displayed answer. The fallback can be text, a number, a cell reference, or another formula.

IFERROR is a presentation and control tool, not a repair mechanism. It does not fix the underlying calculation, supply missing data, or make an invalid operation mathematically valid. It changes the result shown by the formula, which can make reports easier to read but can also conceal problems if used without thought.

Syntax and how Excel evaluates it

The syntax is =IFERROR(value, value_if_error). Both arguments are required, and Excel uses a comma between them in the standard Microsoft 365 settings. In some regional settings, Excel uses a semicolon as the argument separator; that is a localization difference, not a different function.

value is the expression to calculate. It may be a basic calculation, a cell reference, or a nested function such as XLOOKUP.
value_if_error is the replacement returned when the first argument evaluates to an error. Use "" for an empty-looking result, a number such as 0, or a clear message in quotation marks.

For example, with the number 120 in A2 and zero in B2, =IFERROR(A2/B2,"Cannot calculate") returns Cannot calculate, because the division would otherwise return #DIV/0!. If B2 contains 6 instead, the same formula returns 20. IFERROR therefore preserves valid results and substitutes only when the calculation returns an error.

It catches error values produced by the expression, including common errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. In current Excel versions, it can also handle errors such as #SPILL! and #CALC! when they are returned by the expression. It does not catch ordinary results such as zero, FALSE, or an empty cell; those are not errors.

Common uses, with worked examples

A frequent use is avoiding a divide-by-zero message in a ratio or unit calculation. Suppose A2 contains total revenue and B2 contains the number of units. With revenue of 120 and units of 0, the formula below returns 0 as a fallback. With revenue of 120 and units of 5, it returns 24.

Assuming revenue is in A2 and units are in B2, enter this in C2:

=IFERROR(A2/B2,0)

The result is 0 when B2 is zero. That may be suitable for a chart or a report where zero is the chosen display convention, but it can be misleading: zero could be read as a genuine rate rather than an unavailable calculation. A descriptive message can make the distinction clearer.

With revenue in A2 and units in B2, the following formula returns a message if the division fails:

=IFERROR(A2/B2,"No units entered")

If B2 is 0 or blank, the result is No units entered; if B2 is 5 and A2 is 120, it is 24. A blank cell used as a divisor is treated like zero in this calculation. Choose a fallback that makes sense for the people who will read or reuse the result.

IFERROR is also used to handle values that cannot be converted to numbers. For example, VALUE converts numeric text such as "42" into the number 42, but it returns an error for text such as "pending". Assuming A2 contains the text pending, this formula returns the words Invalid number:

=IFERROR(VALUE(A2),"Invalid number")

This can make an imported-data sheet easier to scan, but it does not convert the invalid text into a usable number. If the source data needs correction, keep a way to identify which entries failed rather than treating every failed conversion as though it were simply absent.

Using IFERROR with lookups

A lookup can return #N/A when its requested item is not in the search range. Wrapping the lookup in IFERROR can replace that result with a message. In the example below, IDs are in A2:A5, their prices are in B2:B5, and the requested ID is in E2. If E2 contains an ID that is not in A2:A5, the formula returns No matching ID; if it matches, it returns the corresponding price.

With IDs in A2:A5, prices in B2:B5, and the lookup ID in E2, use:

=IFERROR(XLOOKUP(E2,A2:A5,B2:B5),"No matching ID")

For instance, if E2 is P-103 and the matching price in B4 is 18, the result is 18. If E2 contains P-999, the result is No matching ID. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. The fallback message is useful when a missing match is expected, but it can also hide other errors inside the lookup expression.

For a lookup where only a missing item needs special handling, IFNA is often more precise than IFERROR. IFNA substitutes a fallback for #N/A but allows other errors to remain visible. That distinction can help expose a broken range reference or an unexpected issue instead of presenting it as a routine “not found” result.

Choosing a useful fallback

The replacement should communicate what the error means in the context of the sheet. A numeric zero is convenient for further arithmetic, but it says “the value is zero,” not “the value could not be calculated.” A text message explains the situation to a reader, but text in a result column can affect later calculations. An empty string makes a cell look blank, but it can make missing or invalid results difficult to notice.

Consider these points before choosing the fallback:

Will another formula use the result? A number may be more practical than text for calculations, while a message may be better for a report intended for people.
Does the fallback preserve the meaning? If an error means “not measured,” using zero can distort averages, totals, or charts.
Should the error be visible during review? A clear label can help users find missing input; a blank can make an incomplete sheet look finished.
Could different errors require different responses? If so, handling only one error type may be safer than replacing every error with the same value.

Also note that an empty-looking result from "" is not the same thing as a genuinely empty cell. The formula remains in the cell, and other functions may treat the result differently from a cell that has never contained a value. For example, a formula returning an empty string is still a formula and can affect checks based on whether a cell contains a formula.

IFERROR compared with IFNA and IF

IFERROR is broad: it catches any error returned by its first argument. This is convenient when all possible errors should receive the same treatment, but it can suppress errors that signal a real flaw. IFNA is narrower and catches only #N/A, commonly used for an unavailable lookup result. When a lookup fails because an ID is absent, IFNA can show a “not found” message while leaving unrelated errors exposed.

IF tests a logical condition instead of catching an error. For example, if you want to avoid dividing by zero because the denominator is zero, an IF formula can check the denominator before choosing a result. With the numerator in A2 and denominator in B2, this returns 0 when B2 is zero and otherwise performs the division:

=IF(B2=0,0,A2/B2)

That formula handles a zero denominator explicitly, while IFERROR would also catch other errors from the division, such as a problem caused by unsuitable text in an input cell. The better choice depends on the intention: use IF for a condition you can identify, IFNA for a missing-match error, and IFERROR when a general fallback for errors is genuinely appropriate.

Common problems and ways to diagnose them

The main risk is hiding a problem that should be fixed. If a formula refers to the wrong range and IFERROR turns the resulting error into zero, a report may look plausible while being wrong. During development, leave the error visible or use a specific text fallback so that failures stand out. Once the workbook is understood, decide whether a quiet fallback is appropriate for its users.

IFERROR will not catch every kind of mistake. A formula with invalid syntax may be rejected when entered, before it can calculate. A formula that is syntactically valid but refers to a name Excel does not recognize may produce an error that IFERROR can replace. It also will not replace a result simply because that result seems illogical: a negative quantity, an unexpectedly large number, or the number zero is not automatically an error.

When investigating a formula that appears to work but may be masking errors, try these checks:

Temporarily remove IFERROR and inspect the result of the original expression.
Check the referenced cells and ranges, including whether the source values are blank, text, or numbers.
Confirm that the fallback is appropriate for the calculation and does not make missing data look like a valid measurement.
Test both a normal input and an input that should trigger the fallback.

For a range-producing expression in modern Excel, IFERROR can also return an array of results and substitute for errors within that result. This can be useful with dynamic-array formulas, but the output may spill into neighboring cells, so ensure the intended output area is clear. A blocked spill is a separate issue to investigate, even if a surrounding error-handling formula changes what is displayed.

Frequently Asked Questions

Does IFERROR hide all Excel errors?
It replaces an error returned by its first argument with the chosen fallback, including common errors such as #DIV/0! and #N/A. It does not catch ordinary values such as zero or FALSE, and it cannot repair invalid formula syntax that Excel will not accept.

How do I make IFERROR return a blank?
Use an empty string as the second argument: =IFERROR(A2/B2,""). Assuming the numerator is in A2 and denominator in B2, it shows a blank-looking result when the division errors. The cell still contains a formula, so it is not identical to a truly empty cell.

Is IFERROR better than IFNA?
Not in every case. IFERROR handles any error, while IFNA handles only #N/A. For a lookup where the only expected problem is that an item is missing, IFNA can reveal other errors that a broad IFERROR fallback might hide.

Can I use text as the IFERROR result?
Yes. Put the text in straight double quotation marks, as in =IFERROR(A2/B2,"Check input"). If another formula needs to perform arithmetic on the result, remember that text is not a numeric value.

Why does IFERROR return zero when the cell looks blank?
A blank cell used as a divisor is treated as zero, so a division by that cell can produce #DIV/0!. IFERROR then returns its fallback. Check whether the input is genuinely populated and decide whether a message or blank is clearer than zero.

Practical rule of thumb

Use IFERROR when you know which errors may occur and have a meaningful fallback for them. Prefer a specific message when people need to notice missing or invalid information, and consider IFNA when only a missing lookup result should be handled. If a value is essential to the workbook’s accuracy, investigate the source of the error rather than automatically hiding it. That approach keeps formulas readable without making a clean-looking spreadsheet seem more reliable than its data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.