Excel IFERROR Function: Syntax, Examples, and Tips

Formulas & functions · Updated November 20, 2026

Microsoft Excel’s IFERROR function returns a value you choose when a formula produces an error; otherwise, it returns the formula’s result. For example, with a quantity in B2 and a total in A2, =IFERROR(A2/B2,0) displays 0 instead of a division error when B2 is zero.

What IFERROR Does

IFERROR is a worksheet function for handling errors in calculations and other expressions. It checks the result of its first argument, called the value. If that result is an error, Excel returns the second argument, called value_if_error. If the first result is not an error, Excel returns that result unchanged.

This makes IFERROR useful when a formula may encounter a predictable problem, such as dividing by zero or looking up a value that is absent. It can make a report easier to read by replacing an error with a message, a blank-looking result, or a suitable numeric value. It does not repair the underlying data or calculation, however; it changes what the cell displays when an error occurs.

Excel errors can signal different issues, including #DIV/0! for division by zero, #N/A when a value is unavailable, #VALUE! for an unsuitable value or argument, #REF! for an invalid reference, #NAME? for text Excel does not recognize as a name, #NUM! for a numeric problem, and #NULL! for an invalid range intersection. Current Excel versions can also produce errors such as #SPILL! and #CALC!. IFERROR can provide a fallback for any error result.

Syntax and How the Arguments Work

The function takes two arguments in this order: the expression or reference to evaluate, followed by the value to return if that evaluation produces an error. In Excel’s function syntax, these arguments are shown as value and value_if_error. The second argument is required; it can be a number, text, a reference, or another expression.

For the example, assume A2 contains 24 and B2 contains 6. The formula evaluates the division and returns 4, because the division succeeds:

With the total in A2 and the quantity in B2, enter =IFERROR(A2/B2,0). For A2 = 24 and B2 = 6, the result is 4.

Change B2 to 0 and the division produces #DIV/0!, so the same formula returns 0. This fallback is a decision about presentation and meaning, not a universal correction. If zero would be mistaken for a real measured result, a message or blank-looking output may be more appropriate.

Excel uses commas between arguments in the formulas shown here. Some regional settings use semicolons as argument separators; if Excel rejects a formula copied from another region, check the list separator configured for your installation. The function name and argument order remain the same.

Practical Examples

A common use is calculating a rate, unit price, or average where a denominator may be zero. Suppose column A contains sales totals and column B contains item counts. A row with no items cannot produce a valid per-item amount.

With each row’s sales total in A2 and item count in B2, enter =IFERROR(A2/B2,"No items"). If A2 is 120 and B2 is 4, the result is 30; if B2 is 0, the result is No items.

You can also handle a missing lookup result. For example, a lookup may return #N/A when an ID is not present in a list. With IDs in A2:A10, names in B2:B10, and the ID to find in E2, enter =IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found"). If E2 matches the ID in A4, the formula returns the corresponding name from B4; if it matches no ID, it returns Not found. VLOOKUP is only one way to perform this task; newer Excel versions also offer lookup functions such as XLOOKUP.

Another choice is an empty text result. With a possible divisor in B2 and the numerator in A2, enter =IFERROR(A2/B2,""). If A2 is 10 and B2 is 0, the displayed result looks empty. But it is not an actually empty cell: it contains a formula returning a zero-length text string. For example, ISBLANK will not treat that formula cell as blank, and charts or other formulas may handle it differently from a truly empty cell.

To apply a formula to more rows, enter it in the first row and fill it down. Relative references such as A2 and B2 adjust to A3 and B3 in the next row. If a reference must stay fixed while filling, use dollar signs to lock it, as in $A$2. IFERROR does not change the usual rules for relative and absolute references.

Choosing a Useful Fallback

The best fallback depends on what an error means in the worksheet and how people will use the result. A number, text label, or empty text can each be useful, but each has consequences for later calculations, sorting, charts, and review.

  • Use a number only when that number is a valid, clearly understood substitute. Returning 0 can be suitable when a failed calculation should count as zero, but it can also make missing data look like a genuine zero.
  • Use a short message such as Not found when readers need to know that a result is unavailable. Text makes the problem visible, though it cannot be added as if it were a number.
  • Use an empty text result when a cleaner display matters and downstream formulas can handle it. Remember that the cell still contains a formula and is not truly blank.
  • Leave the error visible when it is important to investigate the cause, especially in a working model or quality-control sheet. A visible error can be more informative than a plausible-looking substitute.

Avoid replacing every error with 0 simply to make a worksheet look tidy. A zero can flow into totals and averages, changing the result without drawing attention. In a business report, consider whether the reader needs to distinguish “no result,” “not applicable,” and a real numeric zero; these are different states and may deserve different labels or handling.

IFERROR Compared with IFNA and ISERROR

IFERROR catches all error types. That broad behavior is convenient, but it may hide an error that points to a genuine mistake, such as a broken reference or misspelled function name. If only a missing lookup match is expected, IFNA is often more precise: it supplies a fallback for #N/A while allowing other errors to remain visible.

For example, with IDs in A2:A10, names in B2:B10, and the lookup ID in E2, =IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found") displays Not found for a missing ID but does not silently replace other error types. IFNA is available in modern Excel versions; older versions may require another approach.

ISERROR serves a different purpose: it tests whether a value is an error and returns TRUE or FALSE. It can be useful when the formula needs a distinct action based on that test, but it is not simply a replacement label. IFERROR is usually more concise when the goal is just to provide a fallback. Older Excel workbooks may use IF together with ISERROR; IFERROR was introduced in Excel 2007.

Common Problems and Misconceptions

One frequent misunderstanding is assuming IFERROR prevents errors. It does not stop a division by zero, restore a deleted reference, or make an unavailable lookup succeed. Excel evaluates the first argument; IFERROR then substitutes the fallback if that result is an error. If the underlying cause matters, correct the data or formula rather than masking it.

Another problem is catching too much. A formula wrapped in IFERROR may hide an unexpected #REF! caused by a deleted column, or a #NAME? caused by a misspelled function. When troubleshooting, temporarily remove the wrapper or replace its fallback with a clear diagnostic message so the original issue is easier to see.

IFERROR also does not catch every kind of problem a user might notice. A formula that returns the wrong number without producing an error is still treated as successful. For instance, dividing the wrong cells may give a valid but incorrect answer. Likewise, a misspelled text value in a cell is not itself an Excel error, so IFERROR will not flag it.

If the fallback argument itself evaluates to an error, that error is returned. IFERROR handles errors from its first argument; it does not repeatedly process its own fallback. And if the formula has a syntax error that prevents Excel from accepting it, IFERROR cannot make that invalid formula work.

Using IFERROR in Microsoft Excel

In Microsoft 365 for Windows, type the formula directly into a cell and press Enter, or select the cell and use the formula bar. You can also choose Formulas > Insert Function to open the function dialog and search for IFERROR. The function works in Excel for Microsoft 365, Excel for the web, and current Excel for Mac; the main steps for entering it are similar across these versions.

IFERROR is available in Excel 2007 and later, so it is generally supported in modern workbooks. If a file must be opened in Excel 2003 or earlier, IFERROR is not available there and may appear as an unrecognized function. Compatibility needs matter when sharing files with older installations.

In Microsoft 365, IFERROR can also be used with formulas that return arrays. In dynamic-array scenarios, errors in returned array results can be handled by IFERROR, but test the specific formula and desired output shape in the target version. For ordinary row-by-row calculations, the familiar single-cell examples are usually easier to inspect and maintain.

Frequently Asked Questions

What does IFERROR mean in Excel?
It means “if the evaluated value is an error, return this alternative.” If the value is not an error, Excel returns it normally.

How do I use IFERROR to show a blank?
Use an empty text string as the second argument. The cell will look blank when an error occurs, but it still contains a formula and is not truly empty.

Can IFERROR hide #N/A?
Yes. IFERROR catches #N/A along with other error values. If #N/A is the only error you intend to handle, IFNA can make that intention clearer and leave other errors visible.

Why does IFERROR return zero?
It returns zero when zero is supplied as the fallback and the first argument produces an error. Check whether the first expression is actually failing, and decide whether zero is a meaningful substitute for that failure.

Does IFERROR work with VLOOKUP?
Yes. It can return a chosen message or other fallback when VLOOKUP produces an error, commonly when a lookup value is absent. Consider IFNA if you want to handle only a not-found result.

Is IFERROR available in older Excel versions?
IFERROR is available from Excel 2007 onward, including current Microsoft 365 editions. It is not supported in Excel 2003 and earlier, which may require a different error-handling formula.

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.