Excel IFERROR With IF: How to Handle Formula Errors

In Excel, use IFERROR to return a chosen result when a calculation produces an error, and use IF when the result should depend on a condition. You can combine them—for example, to divide only when a quantity is available and show a useful message if another error occurs.

What IF and IFERROR do

IF tests a logical condition and returns one result when the condition is true and another when it is false. It is useful for rules such as checking whether a quantity is zero, whether a cell is empty, or whether a value meets a target. The function does not, by itself, replace every error a calculation might produce.

IFERROR evaluates a value or expression. If that evaluation returns an Excel error, it returns the alternative you specify; if it does not return an error, Excel returns the original result. That makes it a convenient way to prevent errors such as #DIV/0! or #N/A from appearing in a report.

The key distinction is that IF responds to a condition you define, while IFERROR responds to an error produced during calculation. An empty cell, a zero, or the text Not found is not automatically an error. If you want Excel to treat one of those situations specially, write an explicit condition with IF.

How to combine IF with IFERROR

Put the expression that might fail inside IFERROR, then provide the result to use if an error occurs. You can also put IF around that calculation when you want to check a known condition first. For example, assume A2 contains revenue and B2 contains units sold. The following formula returns a message for a zero quantity and calculates revenue per unit otherwise:

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

With revenue of 125 in A2 and 0 in B2, the result is No quantity. With 125 in A2 and 5 in B2, the result is 25. This is often clearer than allowing division by zero and then hiding the error, because the formula identifies the specific cause and gives the reader a meaningful explanation.

If the calculation could also fail for some other reason—for example, because a cell contains text that cannot be used in the calculation—you can add error handling. Assuming the same revenue and units layout, this version returns a message for zero units and a different message if the division itself produces an error:

=IF(B2=0,"No quantity",IFERROR(A2/B2,"Check inputs"))

The order matters to the meaning: the outer IF checks for zero first, and the inner IFERROR handles errors in the remaining calculation. This kind of combination is useful when you know one condition deserves its own explanation but still want a general fallback for unexpected formula errors.

Use IFERROR to protect a calculation

A common application is division. If a denominator is zero, Excel returns #DIV/0!. If a referenced cell contains an incompatible value, a calculation may return another error. To keep a summary table readable, you can supply a fallback result. Assume A2 contains an amount and B2 contains a divisor; this formula displays zero if the division returns an error:

=IFERROR(A2/B2,0)

If A2 is 125 and B2 is 0, the formula returns 0 instead of #DIV/0!. That result may be appropriate when zero is the agreed reporting convention, but it can also be misleading: a reader might interpret the zero as a genuine measured value. A label, a blank-looking result, or a visible error message may be safer depending on what the spreadsheet is meant to communicate.

For instance, with the same A2 and B2 layout, the formula below returns the text Review value when the calculation errors:

=IFERROR(A2/B2,"Review value")

Choose a fallback that preserves the meaning of the data. A message is often best in a working sheet where someone must investigate a problem. A numeric fallback can be useful for a calculation that is designed to continue through exceptional cases, but it should be documented so that downstream totals and charts are not mistaken for complete results.

Combine IFERROR with a lookup

Lookup formulas can return #N/A when the requested item is absent. In Microsoft 365 for Windows, you can wrap a lookup in IFERROR to show a readable alternative. Suppose A2 contains a product code, and E2:F100 contains a lookup table with codes in column E and product names in column F. This formula returns the product name for a match and Not found if the lookup produces an error:

=IFERROR(VLOOKUP(A2,$E$2:$F$100,2,FALSE),"Not found")

In this example, FALSE requests an exact match, and the number 2 tells VLOOKUP to return a value from the second column of the specified table. The dollar signs keep the lookup range fixed if the formula is copied to other rows. The result is the matched product name when the code exists; if there is no exact match, the formula returns Not found.

Consider whether a blank input should trigger a lookup at all. With the same product-code and lookup-table layout, this version leaves the result empty when A2 is empty, and otherwise looks up the code:

=IF(A2="","",IFERROR(VLOOKUP(A2,$E$2:$F$100,2,FALSE),"Not found"))

The two double quotes inside the first IF represent an empty text result. The cell is not truly empty: it contains a formula that displays nothing. That distinction can matter to other formulas, filters, and checks for blank cells. Also, the outer condition prevents a blank code from being treated as a failed lookup, while the inner error handler still reports a missing nonblank code.

Choose a useful fallback value

The second argument to IFERROR can be a number, text, a cell reference, or another formula. The right choice depends on how the result will be used. Before replacing errors, ask what a reader should understand and whether the output will feed into further calculations.

Use a message such as Check input when a person needs to find and correct a problem.
Use an empty text result when a display should remain uncluttered, but remember that the cell still contains a formula.
Use a numeric value only when that value has a defensible meaning in the calculation. A replacement zero can affect averages, totals, and charts.
Return another calculation when there is a genuine fallback method, rather than merely concealing a failed first attempt.

A common misconception is that an error-handling formula repairs the underlying data. It does not. IFERROR changes what the formula displays; it does not fix a missing lookup key, correct a bad reference, or make an invalid calculation valid. Keep an error message visible during development, or inspect the source data, if the reason for an error matters to the workbook.

Common problems and mistakes

Hiding errors too broadly: IFERROR catches any error produced by its first argument, not just the one you expected. A misspelled function name, a broken reference, or an unexpected calculation problem may be hidden by the same fallback.
Confusing text with an error: a cell containing the literal text #N/A is not necessarily the Excel error value #N/A. IFERROR handles actual formula errors, not ordinary text that happens to look like one.
Using zero without checking its effect: a fallback of zero can make totals appear complete and can change averages. Decide whether a missing result should count as zero or remain identifiable as missing.
Using the wrong argument order: IFERROR takes the expression to evaluate first and the fallback second. Reversing the intended roles can produce unexpected results.
Forgetting regional separators: this article uses commas, as in the standard Excel syntax for the stated Windows setup. Some regional settings use semicolons between function arguments. If Excel rejects a formula with commas, check the separators expected by your installation.

When debugging, temporarily remove the error handler or replace its fallback with a visible label. That lets you see whether the original expression returns #N/A, #VALUE!, #REF!, or another error. Once the cause is understood, decide whether to correct the formula, correct the data, add a specific condition, or keep a fallback for legitimate exceptions.

IFERROR or IFNA?

IFNA is a narrower alternative: it replaces the #N/A error but leaves other error types visible. For a lookup where a missing match is an expected outcome, that can be more precise than IFERROR. It helps reveal unrelated problems, such as an invalid reference, instead of quietly turning every error into the same message.

Assume A2 contains a product code and E2:F100 contains codes and product names, as in the lookup example above. This formula shows Not found for a missing match but does not suppress other error types:

=IFNA(VLOOKUP(A2,$E$2:$F$100,2,FALSE),"Not found")

Use IFERROR when any error from the expression should lead to the same fallback. Use IFNA when only a missing-value result is expected and other errors should remain visible. Use IF when you can state a meaningful condition directly, such as testing for a zero denominator before dividing. These functions solve related but different problems, and a formula can combine them when each layer has a clear purpose.

Frequently Asked Questions

Can I use IFERROR inside an IF statement in Excel?
Yes. Place IFERROR in either result branch of IF, or use IFERROR around an expression that includes IF. Choose the arrangement that makes the condition and fallback easiest to understand.

Does IFERROR hide every Excel error?
It replaces any error returned by the expression it evaluates, including errors such as #DIV/0! and #N/A. It does not replace ordinary text, zero, or a blank result just because those values seem unexpected.

How do I show a blank instead of an error?
Use an empty text result as the second argument to IFERROR. The displayed cell will look blank, but it still contains a formula and is not necessarily treated like a genuinely empty cell in other operations.

Should I use IFERROR or IF to prevent division by zero?
If zero is the known condition you want to handle, test the denominator with IF and return a meaningful result. Use IFERROR when you also need to catch calculation errors that may arise for other reasons.

Why does IFERROR show my fallback when the formula should work?
The underlying expression is returning some kind of error. Temporarily remove the wrapper or show a diagnostic label, then check the referenced cells, lookup match, function spelling, and data types. The fallback hides the symptom; it does not identify or fix the cause.

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.