IFERROR in Excel: Syntax, Examples, and Common Fixes

Formulas & functions · Updated November 18, 2026

In Excel, IFERROR checks whether a calculation returns an error and displays a value you choose instead; its basic pattern is =IFERROR(value, value_if_error). For example, if A2 contains 24 and B2 contains 0, =IFERROR(A2/B2,"Cannot divide") displays “Cannot divide” instead of the #DIV/0! error.

What IFERROR does

IFERROR is a worksheet function that lets you handle an error as part of a formula. Excel first evaluates the expression in its first argument. If that result is an error, the function returns the fallback value in its second argument. If the expression is not an error, IFERROR returns the original result unchanged.

It can handle common Excel error values such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. This makes it useful when a formula may fail for a normal, expected reason—for example, when a lookup value is not in a list or a denominator can be zero.

IFERROR changes what the worksheet displays; it does not repair the calculation or make the underlying data valid. A fallback such as zero or “Not found” can make a report easier to read, but it can also conceal an issue that deserves attention. Choose the fallback to match what the error means in that particular calculation.

Syntax and how the arguments work

The syntax is =IFERROR(value, value_if_error). Both arguments are required:

  • value is the calculation, cell reference, or formula to evaluate.
  • value_if_error is the result to return if that evaluation produces an error. It can be text, a number, a cell reference, or another formula.

For a simple example, with a number in A2 and another number in B2, =IFERROR(A2/B2,0) returns the quotient when division works and zero if B2 is zero. If A2 is 24 and B2 is 6, the result is 4; if B2 is 0, the result is 0. The fallback does not appear when the first argument calculates successfully.

Text values in formulas need straight double quotation marks. For instance, the word Not found must be entered as "Not found" inside a formula. In Excel for Microsoft 365 on Windows, enter a formula in a cell or the formula bar and press Enter. The formulas here use commas between arguments; in some regional settings, Excel expects semicolons instead.

Practical examples for everyday worksheets

Prevent a divide-by-zero message. Suppose A2 contains units sold and B2 contains the number of orders. With units in A2 and orders in B2, =IFERROR(A2/B2,0) returns the average units per order, or zero if there are no orders. If A2 is 24 and B2 is 6, the result is 4. If B2 is 0, the result is 0. This can be suitable for a summary, but if zero would be misleading, use an explanatory message instead.

Handle a missing lookup result. Suppose product codes are in A2:A10, prices are in B2:B10, and the code to search for is in E2. With that layout, =IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found") returns the matching price from the second column of the range, or “Not found” if the lookup produces an error. If E2 contains a code that is absent from A2:A10, the result is “Not found”. The FALSE argument asks VLOOKUP for an exact match.

Display a blank instead of an error. If A2 is a numerator and B2 is a denominator, =IFERROR(A2/B2,"") returns the division result when possible and an empty text string if the division errors. With A2 equal to 24 and B2 equal to 0, the cell looks blank. It is not necessarily the same as a genuinely empty cell: the cell still contains a formula, and functions such as COUNTA can count it.

These patterns can be adapted to many calculations, but the replacement should communicate a meaningful outcome. A message may be best for a missing lookup, while a blank may suit a chart helper column. Zero is only appropriate when zero is a valid and honest substitute for the failed result.

Choosing a useful fallback value

A good fallback makes the spreadsheet clearer without disguising what happened. Before using IFERROR, ask whether a user needs to distinguish a legitimate zero from a failed calculation. If the distinction matters, returning text such as “Check input” or “No match” is usually more informative than returning 0 or an empty string.

  • Use an explanatory message when someone needs to investigate missing or invalid data.
  • Use zero only when treating the error as zero fits the meaning of the measure and will not distort totals or averages.
  • Use an empty text string when a blank-looking display is helpful, while remembering that the cell still contains a formula.
  • Use another calculation when there is a defensible alternate value, and document the rule so other workbook users understand it.

For example, replacing every lookup error with zero might make a price report look complete, but it can cause a missing product to appear to have a real price of zero. A clear message preserves that distinction. Similarly, hiding a division error in a key operational measure might make a dashboard look tidy while masking missing input data.

IFERROR compared with IFNA and other approaches

IFERROR is broad: it replaces any error in its first argument. IFNA is narrower and replaces only #N/A. That distinction matters when a formula can fail for several reasons. A missing lookup may reasonably call for “Not found,” but a #REF! error caused by a deleted reference might indicate a formula that needs repair, not a normal missing match.

With lookup codes in A2:A10, corresponding values in B2:B10, and a search code in E2, =IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found") returns “Not found” for an unavailable code while allowing a different error to remain visible. If E2 contains no matching code, the result is “Not found”. Use IFNA when the expected problem is specifically a missing match and you want other errors to be easier to spot.

IFERROR is also different from IF. IF tests a logical condition you specify; IFERROR reacts to an error produced by an expression. For example, testing whether a denominator is zero before division can make the rule explicit, while IFERROR is convenient when the expression may have several possible error conditions. Neither approach can determine whether a result is logically sensible: a formula can return the wrong number without producing an Excel error.

Some lookup functions have their own missing-result option. XLOOKUP, available in Microsoft 365 and Excel 2021 or later, includes an argument for what to return when no match is found. That can be clearer than wrapping XLOOKUP in IFERROR when you only want to handle a missing match. IFERROR remains useful for other calculations and for formulas where broader error handling is intentional.

Common problems and how to diagnose them

The formula returns the fallback unexpectedly. Check the inputs and the calculation inside IFERROR. For a lookup, verify that the search value actually matches the list, including spaces and text-versus-number differences. For division, check whether the denominator is zero or a formula returning zero. IFERROR may be working as written even if the underlying cause is a data issue.

The fallback hides a broken formula. A broad wrapper can conceal #REF! from a deleted cell reference or another unintended error. Temporarily remove IFERROR, or inspect the inner expression in a separate cell, to reveal the original result. Once diagnosed, decide whether the error is an expected case worth handling or a problem the workbook should expose.

The cell looks empty but is not empty. A fallback of "" produces empty text, not a truly blank cell. This can affect counts, downstream formulas, and checks for empty cells. If later calculations depend on the result, test how those formulas treat empty text rather than assuming it behaves exactly like an unused cell.

Excel rejects the formula as typed. IFERROR cannot catch a formula-entry or syntax problem that prevents Excel from parsing the formula. Check the opening and closing parentheses, commas or regional list separators, and quotation marks around text. Also confirm that both required arguments are present.

The result is wrong but no error appears. IFERROR catches error values, not incorrect logic. A lookup can return a valid but unintended match, and a calculation can use the wrong cell reference yet still return a number. Check the formula’s references, match settings, and assumptions separately.

Availability and good spreadsheet practice

IFERROR is available in Excel 2007 and later, including Microsoft Excel for Microsoft 365 on Windows, Mac, and the web. It is not a Microsoft 365-only function. Its basic syntax is the same across those platforms, although interface details and regional argument separators can differ. In Excel for the web, formulas can be entered directly in worksheet cells; there is no separate desktop-only step required to use IFERROR.

For a reliable workbook, apply error handling selectively rather than wrapping every formula automatically. A useful workflow is to build and check the underlying calculation first, identify which errors are expected, and then add a fallback for those cases. Keep error messages visible during development so that a typo, broken reference, or unexpected input is not silently converted into a normal-looking value.

Consider adding a note to a report or documentation when a fallback has business meaning—for example, whether a missing result is treated as zero or excluded. This helps another person interpret the output correctly. In shared workbooks, consistency also matters: use the same wording for the same condition and avoid a mixture of blanks, zeros, and messages unless each has a distinct meaning.

Frequently Asked Questions

What does IFERROR mean in Excel?
IFERROR means Excel evaluates a value or calculation and returns a specified alternative if the result is an error. If there is no error, it returns the calculated result.

How do I use IFERROR to show a blank?
Put an empty text string in the second argument, such as =IFERROR(A2/B2,""). With A2 as the numerator and B2 as the denominator, the cell displays blank when division errors. The formula remains in the cell, so the result is not a truly empty cell.

Does IFERROR hide every kind of error?
It handles Excel error values produced by its first argument, including common errors such as #DIV/0!, #N/A, and #REF!. It does not catch a formula that Excel cannot parse, nor does it identify a logically incorrect result that contains no error.

Should I use IFERROR or IFNA for VLOOKUP?
Use IFNA when you want to replace only a missing-match #N/A result and leave other errors visible. Use IFERROR when you deliberately want to replace any error the lookup expression can return. For newer lookup workflows, XLOOKUP also has an argument for a missing-match result.

Why does IFERROR return zero when I expected a result?
If zero is the second argument, the first calculation is producing an error and IFERROR is showing that fallback. Inspect the calculation without the wrapper and check its inputs, references, and lookup match conditions to find the cause.

Can IFERROR fix a formula error?
It can replace an error value in the displayed result, but it does not correct the source formula, repair a broken reference, or validate the answer. Diagnose the original error before deciding whether to keep a fallback.

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.