Excel IF No Error: Use IFERROR the Right Way

To return a result when an Excel calculation has no error and show an alternative when it does, use =IFERROR(value, value_if_error). For example, with a numerator in A2 and a divisor in B2, =IFERROR(A2/B2,"Cannot divide") returns the quotient when division works and the message when it produces an error.

What “if no error” means in Excel

Excel does not have a function named IFNOERROR. The function most people mean is IFERROR: it tries a calculation, returns its normal result if that calculation succeeds, and substitutes a value you choose if it returns an error. The calculation can be a simple operation, a lookup, or a longer expression.

For example, division by zero produces the #DIV/0! error. If B2 contains zero, the sample formula above displays “Cannot divide”; if B2 contains 4 and A2 contains 12, it displays 3. The function does not change the underlying inputs or repair the cause. It controls what the formula returns when an error occurs.

This is useful for reports where an error message would be distracting, but it also creates a responsibility: decide whether the fallback should explain the issue, show a value such as zero, or leave the cell visually blank. A quiet result can make a broken calculation less obvious, so the choice should fit the worksheet’s purpose.

How IFERROR works

IFERROR has two arguments: the expression to evaluate and the fallback to return if that expression results in an error. The first argument can be a formula or a value. The second can be text, a number, a blank-looking text value, or another calculation. If the first argument does not return an error, Excel returns its result unchanged.

With the numerator in A2 and the divisor in B2, this formula returns the actual calculation when possible and the number zero when it fails: =IFERROR(A2/B2,0). If A2 is 12 and B2 is 3, the result is 4. If B2 is 0, the result is 0. That second result may look convenient, but it can be misleading if zero could also be a legitimate answer.

IFERROR catches Excel errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, and #NUM!, as well as errors produced by newer features. It does not treat every unusual value as an error: an empty cell, the number zero, or ordinary text is not automatically replaced by the fallback. The fallback itself should also be valid; if it produces an error, that error can still appear.

Practical formulas for common calculations

For a ratio, decide what a reader should see when the denominator is missing or zero. With revenue in A2 and units sold in B2, this formula displays a label instead of an error: =IFERROR(A2/B2,"No units"). If revenue is 600 and units are 30, the result is 20. If units are zero, the result is “No units.” This is often clearer than displaying zero, which could be mistaken for a real per-unit result.

IFERROR can also protect a calculation that might fail for reasons other than division. With a date in A2 and a number of days in B2, a basic date addition is =IFERROR(A2+B2,"Check date or days"). If A2 is a valid Excel date serial and B2 is 7, Excel returns the date one week later. If a cell contains text that cannot be used in the addition, the fallback message appears. The message can guide a user toward the input that needs checking.

For a calculated average, it may be better to show a meaningful note than to disguise an empty-data condition. With values in A2:A10, =IFERROR(AVERAGE(A2:A10),"No numeric values") returns the average when Excel can calculate one and the message when it cannot. When building a worksheet, test both a normal case and an error case; a formula that looks right with one sample row may behave differently when a range is empty or contains unexpected text.

Using IFERROR with lookups

A frequent reason to use IFERROR is to replace the #N/A result from a lookup when a requested item is missing. With product IDs in A2:A100, product names in B2:B100, and the ID to search for in E2, this formula returns a name or a message: =IFERROR(XLOOKUP(E2,A2:A100,B2:B100),"Product not found"). If E2 matches an ID, Excel returns the name from the corresponding row; if it does not match, the fallback appears.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In those versions, it also has an optional argument for a not-found message, so the same layout can use =XLOOKUP(E2,A2:A100,B2:B100,"Product not found"). That approach is more specific: it supplies a message for a missing match without automatically replacing other kinds of errors that might arise. It is often preferable when the only expected problem is an absent ID.

With older lookup formulas, IFERROR can be useful when a function returns an error for an absent match. But wrapping an entire lookup in IFERROR may also hide a different problem, such as a misspelled function name or an invalid reference. If a workbook needs to distinguish “not found” from “formula is broken,” use a narrower error check or investigate the source rather than masking every error with the same text.

IFERROR, IFNA, and ISERROR: which should you choose?

Choose the function based on what counts as an expected failure in your worksheet:

IFERROR replaces any error produced by its first argument. Use it when several kinds of calculation failure should lead to the same fallback.
IFNA replaces only the #N/A error. Use it for a lookup where a missing match is expected, but other errors should remain visible. IFNA is available in Excel 2013 and later.
ISERROR tests an expression and returns TRUE if it produces an error, or FALSE if it does not. Use it when you need an explicit logical test, rather than simply substituting a fallback.

With a value in A2 and a divisor in B2, an ISERROR test can be written as =IF(ISERROR(A2/B2),"Check inputs",A2/B2). It displays the message if the division errors and otherwise displays the quotient. However, the division appears twice in the formula, which can make long expressions harder to maintain and may require Excel to calculate the same work more than once. IFERROR is usually the simpler choice for this pattern.

Use IFNA rather than IFERROR when only a missing lookup result should be treated as normal. This distinction helps surface unrelated issues instead of making them look like ordinary missing data. Conversely, IFERROR is suitable when a formula is intentionally designed to handle several possible errors and the same fallback is useful for all of them.

Blank cells, zero, and blank-looking results

A common question is whether IFERROR can leave a cell blank when a formula fails. It can return an empty text string as the fallback. With a calculation in A2/B2, use =IFERROR(A2/B2,""). The cell will look empty if the division errors, but its formula is still present. An empty string is text, not the same thing as a genuinely empty cell, and other formulas may treat those cases differently.

IFERROR will not replace a valid zero or an empty input merely because it looks incomplete. If you want a blank output when the numerator in A2 is empty, as well as a blank fallback for calculation errors, use =IF(A2="","",IFERROR(A2/B2,"")). This checks the numerator first, then attempts the division. If A2 contains 12 and B2 contains 3, the result is 4; if A2 is empty, the displayed result is blank.

Think carefully before using zero as a fallback. A chart may plot that zero, a total may include it, and a later average may interpret it as a genuine measurement. A text fallback is easier to notice but may not work in downstream arithmetic. A blank-looking string can preserve a cleaner presentation, yet it can also conceal missing or invalid data. Match the fallback to how the result will be read and used.

Common problems and safer habits

The most important risk is hiding an error that deserves attention. If a formula unexpectedly returns “Not found” for every row, the problem might be an incorrect lookup range or mismatched data types rather than genuinely missing records. If a formula returns zero, users may assume the calculation succeeded. During development, leave errors visible or use a descriptive message so that unexpected conditions are easier to diagnose.

Other useful checks include:

Test a normal input, an input that should cause an error, and a boundary case such as a blank cell or zero.
Use a fallback that tells the reader what happened, such as “Check denominator,” when that is more useful than a generic “Error.”
Apply IFERROR to the smallest expression that needs handling. Wrapping an entire complex formula can conceal which part failed.
Check whether a fallback changes later calculations. Text and empty strings behave differently from numbers.
Inspect the underlying formula and source data if the fallback appears more often than expected.

In Excel for Microsoft 365 on Windows, enter the formula in the cell where you want the result and press Enter. The formulas here use commas between arguments, as is standard in many installations. Excel’s argument separator can depend on regional settings; if your copy expects semicolons, use the separator shown by Excel rather than treating the displayed formula as a different function. The function names and arguments remain the same.

Frequently Asked Questions

What is the Excel formula for IF no error? Use IFERROR with the calculation first and the fallback second. For example, with a calculation in A2/B2, =IFERROR(A2/B2,"") returns the quotient when it works and a blank-looking result if it produces an error.

Does IFERROR return the original value when there is no error? Yes. If its first argument evaluates without an error, IFERROR returns that result rather than the fallback. For example, with A2 equal to 12 and B2 equal to 3, =IFERROR(A2/B2,"Error") returns 4.

How do I show blank if a formula has an error? Put an empty text string in IFERROR’s second argument. With a calculation in A2/B2, =IFERROR(A2/B2,"") displays nothing when the calculation errors. Remember that this is an empty string returned by a formula, not a truly empty cell.

Should I use IFERROR or IFNA for a lookup? Use IFNA if a missing match is the only error you intend to handle; it leaves other errors visible. Use IFERROR when several kinds of errors should produce the same fallback. XLOOKUP in Microsoft 365 or Excel 2021 and later can also take a not-found message directly.

Why does IFERROR show zero instead of a blank? The second argument determines the fallback. If the formula uses zero as its second argument, Excel returns zero when the first argument errors. Replace that argument with an empty string if you want a blank-looking result, or with a descriptive message if readers need to know the calculation failed.

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.