IFERROR Not Working in Excel? Find and Fix the Cause

If IFERROR is not working in Excel, first check that the formula is written as =IFERROR(your_formula, value_to_show_if_an_error_occurs) and that the formula inside it really returns an error. IFERROR only substitutes a result when its first argument evaluates to an Excel error; it does not correct a wrong answer, turn every unexpected result into a blank, or fix every workbook problem.

What IFERROR does—and what it does not do

IFERROR evaluates an expression and returns its result when there is no error. If the expression produces an error value, it returns the alternative you specify. Common errors it can handle include #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. The alternative can be text, a number, a blank-looking empty string, or another calculation.

For example, with a dividend in A2 and a divisor in B2, this formula returns the division result unless the calculation produces an error; if B2 is zero, it returns blank text:

=IFERROR(A2/B2,"")

That blank is not the same as an actually empty cell. The formula remains in the cell, and the result is a zero-length text string. This matters for some counts, charts, and downstream formulas. IFERROR also does not detect a result that is simply incorrect. If a lookup finds the wrong record or a calculation returns 0 when you expected 10, there may be no Excel error for IFERROR to catch.

Check whether the formula actually contains an error

A frequent reason the function seems ineffective is that the underlying formula returns a valid value rather than an error. A lookup that cannot find a match may return an empty string or 0 if the formula was designed that way. Likewise, a cell displaying a dash may contain a number formatted to show as a dash. IFERROR leaves all such results unchanged.

Test the expression without the wrapper in a spare cell, or inspect its components with Excel’s Formula Bar. If the unwrapped calculation returns a value, IFERROR is behaving as designed. If you need to handle a particular condition such as a missing lookup match, use a function or logical test that targets that condition rather than expecting IFERROR to recognize it.

There is also a difference between an error in the formula and an error-looking message stored as ordinary text. A cell containing the text “#N/A” is not necessarily the #N/A error value. IFERROR reacts to actual Excel errors, not text that merely resembles one. You can check a suspect cell by selecting it and looking at the Formula Bar and the cell’s behavior in related calculations.

Verify the syntax, separators, and references

IFERROR needs two arguments: the expression to evaluate and the result to return if that expression errors. Check that both are present, the parentheses are balanced, and text results are enclosed in straight double quotation marks. A missing argument, an unmatched parenthesis, or an invalid reference can cause Excel to reject the formula or display an error before IFERROR can do what you intended.

With a lookup value in E2, possible lookup keys in A2:A10, and return values in B2:B10, this formula displays “Not found” if the lookup calculation returns an error:

=IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")

Check the reference range as well as the IFERROR syntax. In this example, the lookup value must be in the first column of A2:B10, and the return column number is 2. If the lookup table was moved, a reference now points to the wrong cells, or the return column number is invalid, the formula may produce an error. IFERROR can replace that error with its fallback, but it cannot make the lookup table or reference correct.

Excel normally uses commas between function arguments in the formulas shown here. Depending on Windows regional settings, the list separator may instead be a semicolon; if Excel reports a formula problem, use the separator expected by your installation. The underlying function is the same. Excel for Mac can also use regional separators, so check the system’s settings rather than assuming the Windows default applies.

Check calculation mode and formula entry

If a formula appears unchanged after you edit its inputs, the workbook may be set to manual calculation. In Excel for Microsoft 365 on Windows, select Formulas > Calculation Options > Automatic. Then recalculate the workbook. A stale displayed result can make it look as if IFERROR ignored a new error or failed to update its fallback, even though the formula itself is valid. Excel for Mac has calculation options on the Formulas tab as well; the available interface can vary slightly by release.

Also confirm that the cell contains a formula rather than text. If the entry begins with an apostrophe, or the cell was formatted as Text before the formula was typed, Excel may display the formula characters instead of calculating them. Change the cell format to General, edit the cell, and press Enter. Be careful when changing formats: changing Text to General alone does not always recalculate an existing text entry; re-entering the formula may be necessary.

Check for a leading space or apostrophe before the equals sign, and make sure the formula was entered in the intended cell. If a formula is copied down a column, inspect relative references too. A reference that shifts from B2 to B3 is often correct, but a lookup range that shifts unintentionally may need absolute references. IFERROR does not prevent references from changing when a formula is copied.

Why an error can remain visible

IFERROR catches errors produced by its first argument, but the fallback can fail too. If the second argument refers to an invalid cell, divides by zero, or contains another error-producing calculation, Excel displays that new error. Keep the fallback simple while diagnosing the formula; a clear text message or a safe value is easier to verify than a second complicated expression.

Some problems are outside the calculation IFERROR is wrapping. A formula that cannot be parsed, an invalid function name, or a damaged formula may be rejected before it can return a value. Similarly, a workbook-level or layout issue may need to be fixed at its source. For example, a dynamic-array formula can show #SPILL! when cells in its intended output area are occupied. Clear the obstruction or move the formula; hiding the message does not create room for the results.

Finally, confirm that the visible error is coming from the cell with IFERROR. It may be a neighboring formula, a conditional-formatting indicator, or an error displayed by a dependent cell. Select the cell that shows the message and inspect its formula. Wrapping one formula does not change errors in other cells that refer to it or share the same worksheet.

Use IFERROR without hiding useful warnings

Using a blank fallback can make a report look tidy, but it can also conceal broken references or unexpected data. During development, a descriptive fallback often makes the cause easier to spot. For instance, the lookup example can return “Not found” instead of a blank, making missing records visible to someone reviewing the sheet.

Before settling on a fallback, ask what the error means in the context of the calculation. A divide-by-zero error might mean a ratio is not applicable, but it might also reveal a missing input. A lookup error might indicate an ordinary unmatched item, or a spelling inconsistency that should be corrected. Choose an output that communicates the distinction your users need.

Use a descriptive message when a person should investigate the missing or invalid input.
Use a blank only when an empty-looking result is genuinely appropriate and downstream calculations can handle it.
Use a numeric fallback such as 0 only when zero is a meaningful result, not merely a convenient way to suppress an error.
Keep an unwrapped version of a complex formula temporarily while troubleshooting, so you can see the original error.

When IFNA or an explicit test is a better choice

IFERROR is broad: it replaces many kinds of errors, including ones you may want to know about. If the only expected problem is a missing lookup result, IFNA is often more precise because it responds to #N/A rather than masking unrelated errors. With a lookup value in E2, keys in A2:A10, and corresponding results in B2:B10, the following formula displays “Not found” only when the lookup returns #N/A:

=IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")

If the issue is specifically a zero divisor, an explicit test can explain the rule more clearly. With the dividend in A2 and divisor in B2, this formula returns a blank when B2 is zero and otherwise performs the division:

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

This does not catch every possible error in the division; it handles the defined zero-divisor case. That distinction can be useful: targeted logic preserves unexpected errors so they remain visible, while IFERROR is convenient when any error should receive the same fallback. Choose based on what the spreadsheet is supposed to communicate, not just on which formula makes the warning disappear.

A practical troubleshooting sequence

Work through these checks in order, changing one thing at a time. This makes it easier to identify whether the cause is the formula, the inputs, calculation settings, or the workbook layout.

Select the affected cell and confirm that it contains a formula beginning with an equals sign, not formula text.
Read the full formula in the Formula Bar. Check the two IFERROR arguments, quotation marks, parentheses, separators, and cell references.
Temporarily evaluate the first argument on its own in a safe cell. Determine whether it produces an actual Excel error or a valid result.
If the first argument errors, inspect the source inputs and references. For a lookup, check the key, lookup range, and return-column logic; for division, check the divisor.
If the formula is correct but stale, set calculation to Automatic and recalculate. If an array formula is involved, check for occupied spill cells.
Replace the fallback temporarily with a simple, unmistakable message. If an error remains, check whether it comes from the fallback or from a different cell.

Once the cause is understood, decide whether to correct the data or formula, use a narrower handler such as IFNA, or keep IFERROR with a suitable fallback. A formula that suppresses an error is not necessarily a fixed formula; verify the result against the intended calculation.

Frequently Asked Questions

Why does IFERROR still show #N/A?
Check that IFERROR wraps the formula producing the #N/A and that the visible message is an actual error in that same cell. If the #N/A is text, comes from another cell, or appears in an unwrapped part of the worksheet, the wrapper will not replace it. Also verify that the formula was accepted and recalculated.

Does IFERROR work with VLOOKUP?
Yes. IFERROR can wrap a VLOOKUP expression and return an alternative when that lookup produces an error. If you only want to handle a missing match, IFNA is usually more targeted because it leaves other error types visible.

Why does IFERROR return 0 instead of blank?
The second argument controls the fallback. If it is 0, Excel returns 0; if it is an empty string written as two double quotation marks, the result looks blank but is text. Check the full formula for the fallback argument and any cell formatting that might display a numeric result unexpectedly.

Can IFERROR hide every Excel error?
It handles errors returned by the expression it evaluates, but it is not a repair tool for formula-entry problems, blocked spill ranges, incorrect logic, or errors in other cells. Its fallback can also produce an error of its own. Investigate the original cause before using it to suppress a message.

Should I use IFERROR or IFNA?
Use IFNA when the expected condition is specifically #N/A, such as a lookup with no match. Use IFERROR when you intentionally want the same fallback for a wider range of calculation errors. A narrower response can prevent important problems, such as an invalid reference, from being hidden.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.