IFERROR Function in Excel: Syntax, Examples, and Tips

Formulas & functions · Updated November 20, 2026

In Excel, use IFERROR to return a chosen result when a formula produces an error: the syntax is =IFERROR(value, value_if_error). For example, it can replace a division-by-zero error with a message, a blank, or a value you choose; if the calculation succeeds, Excel returns its normal result.

What IFERROR Does

IFERROR evaluates its first argument, called value. If that calculation returns an error, Excel returns the second argument, value_if_error. If it does not return an error, Excel passes the original result through unchanged. This makes the function useful for keeping reports readable when some inputs are missing or some lookup values are not found.

It can catch common worksheet errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. Newer Excel features can also produce errors such as #SPILL! or #CALC!, which IFERROR can handle when they arise inside its first argument. It does not treat an ordinary zero, an empty cell, or the logical value FALSE as an error.

The function is available in current Microsoft 365 Excel and in earlier versions, including Excel 2007 and later. Its purpose is not to repair the underlying calculation. It changes what the worksheet displays when an error occurs, so it is worth choosing a fallback that makes the situation clear rather than concealing an issue that needs attention.

Syntax and How the Arguments Work

The structure has two arguments, separated by a comma in standard English-language Excel installations. The first argument can be a calculation, a cell reference, or another function. The second argument can be a number, text, a reference, or another expression. It is evaluated as the alternative result when the first argument returns an error.

  • value: The calculation or value Excel checks.
  • value_if_error: What Excel should return if that calculation produces an error. This argument is required.

For example, a formula can divide one cell by another. When the denominator is zero, Excel normally returns #DIV/0!. Wrapping the division in IFERROR lets you specify what to show instead. When the denominator is a nonzero number, the formula still returns the quotient, not the fallback.

IFERROR is a worksheet function, not a setting that turns off error checking throughout a workbook. It affects only the expression inside that particular formula. Other formulas can still display errors, and correcting a source error may still be necessary even if a downstream formula has a fallback.

Practical Division Examples

Suppose A2 contains the amount to divide and B2 contains the divisor. In C2, this formula returns a message if the division fails:

With the amount in A2 and divisor in B2, enter =IFERROR(A2/B2,"Not available") in C2. If A2 is 10 and B2 is 0, C2 displays Not available. If A2 is 10 and B2 is 2, C2 returns 5.

A message is useful in a worksheet meant for people to read, but a numeric fallback may be more useful when later calculations need a number. With the same A2 and B2 layout, enter =IFERROR(A2/B2,0) in C2. For 10 divided by 0, the result is 0; for 10 divided by 2, it is 5. Be cautious: the displayed zero does not mean the division actually equaled zero. It means the formula encountered an error and substituted zero.

You can also return an empty text string to make an error result look blank. With the same values in A2 and B2, enter =IFERROR(A2/B2,"") in C2. If B2 is zero, C2 appears blank; if A2 is 10 and B2 is 2, it displays 5. A formula returning an empty string is not the same as an actually empty cell: functions and charts may treat them differently, and the formula remains visible in the formula bar when C2 is selected.

Using IFERROR with Lookups

A lookup can return #N/A when it cannot find the requested item. IFERROR can replace that error with a friendlier message. Suppose D2 contains a product code to find and E2:F100 contains product codes in the first column and descriptions in the second. With that layout, enter =IFERROR(VLOOKUP(D2,E2:F100,2,FALSE),"Not found") in the result cell. If D2 contains a code that is absent from E2:E100, the formula displays Not found; if the code is present, it returns the corresponding description from column F.

The last VLOOKUP argument, FALSE, requests an exact match. That matters for codes and other identifiers: omitting it can allow an approximate match under conditions that may return an unintended result. IFERROR does not make a lookup more accurate; it only changes the result if the lookup expression returns an error.

There is an important trade-off. IFERROR catches every error from the lookup expression, not just “item not found.” If the table reference is broken, the column index is invalid, or another part of the formula has a problem, the same Not found message may hide that different fault. When the only expected error is a missing match, IFNA is often a clearer choice because it handles #N/A specifically and lets other errors remain visible.

Choosing a Useful Fallback

Pick the fallback based on what the result will be used for, not just how tidy the sheet looks. A descriptive message can help someone investigate missing data. A blank can be appropriate in a presentation table where an error would distract, while a numeric fallback can support calculations—but it can also change totals and averages without making the substitution obvious.

  • Use a message when a person should know the calculation could not be completed.
  • Use a blank when an error result should be visually unobtrusive and downstream formulas can handle text.
  • Use a number only when that substitute has a defensible meaning for the analysis.
  • Keep the error visible while building or checking a workbook if the cause still needs investigation.

Consider a sales report where a margin calculation divides profit by revenue. Replacing every error with zero may make a row look like a genuine zero margin, even if its revenue cell is missing. A label such as “Check inputs” communicates that the number is unavailable, while leaving the error visible during review may make the data problem even easier to locate.

Common Mistakes and Misconceptions

The most common mistake is using IFERROR as a blanket cover for every problem in a workbook. A formula that returns “Not found” might actually contain a misspelled function name, an invalid cell reference, or a wrong lookup range. The displayed message can make the sheet look finished even though the formula needs repair. During development, inspect the original formula and its inputs before deciding to suppress the error.

Another misconception is that IFERROR detects bad data in general. It responds to Excel error values, not to every value that seems suspicious. A negative quantity, an implausibly large number, a blank input, or text where a number was expected may not always produce an error. If you need to test a condition such as whether a cell is empty or whether a number is negative, use a suitable logical test rather than relying on IFERROR alone.

Also remember that a blank-looking result can still affect the workbook. An empty text string is text returned by a formula, not a cleared cell. Conversely, a zero may be hidden by a workbook’s number format even when the formula really returned zero. If the display seems inconsistent, inspect the formula bar and the cell’s number format as well as the formula itself.

Finally, IFERROR does not change the source cell that caused the problem. If a referenced cell contains #REF!, wrapping a later calculation may mask that error in the later cell, but the broken reference remains. Fixing the source is usually preferable when the error signals a genuine workbook defect.

IFERROR, IFNA, and Other Options

IFNA is the closest alternative when a formula should handle only a missing lookup result. Unlike IFERROR, it catches #N/A and leaves other kinds of errors visible. That distinction helps separate an expected “no matching item” outcome from a formula or reference problem that deserves attention.

An IF function can test a known condition before doing a calculation. For example, you may want to check whether a divisor is zero and show a specific message in that case. Such a test can make the reason for the fallback explicit, but it may not catch unrelated errors in the calculation. IFERROR is broader and more compact; IF or IFNA can be more precise when you know which condition matters.

For a lookup, newer Excel versions also offer functions with built-in “if not found” options, such as XLOOKUP in Microsoft 365 and Excel 2021 or later. That option can handle a missing match without wrapping the lookup in IFERROR, while still allowing other errors to surface. Choose the approach that matches the Excel versions used by everyone who needs to open the workbook.

How to Troubleshoot an IFERROR Formula

If a formula is showing its fallback unexpectedly, temporarily remove IFERROR or examine the first argument separately. The underlying error often points to the cause: #DIV/0! suggests division by zero, #N/A commonly indicates a failed lookup, and #REF! signals an invalid reference. Check the inputs, range boundaries, and function arguments before restoring the fallback.

If the formula returns the calculation instead of the fallback, Excel did not detect an error in the first argument. That may be correct even if the result is undesirable—for example, a division can return zero without producing an error. Decide whether the issue is truly an Excel error or a separate business rule that needs its own test.

When reviewing a workbook, test both paths: one input set that should calculate normally and one that should trigger the fallback. Confirm that the displayed message or substitute is appropriate, and check whether totals, charts, or later formulas interpret that fallback as intended. This small check helps prevent an error-handling formula from hiding a reporting problem.

Frequently Asked Questions

What does IFERROR do in Excel?

It returns the result of a calculation when that calculation succeeds, and returns a specified alternative when it produces an Excel error. It does not fix the cause of the error or test for every kind of invalid or unexpected data.

Can IFERROR return a blank?

Yes. Use an empty text string as the second argument to make the cell appear blank when the first argument errors. The cell still contains a formula, and its result is not identical to a truly empty cell.

Does IFERROR catch #N/A?

Yes. IFERROR catches #N/A along with other Excel errors. If you want to handle only #N/A and keep other errors visible, use IFNA instead.

Why does IFERROR still return zero?

Zero is a valid calculation result, not an error, so IFERROR passes it through. If you want to treat zero as a special case, add a separate logical test for zero.

Is IFERROR better than IFNA?

Neither is universally better. Use IFERROR when any error in the expression should trigger the fallback. Use IFNA when only a missing-match error should be handled and other errors should remain visible.

Related

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.