IFERROR Formula in Excel: How to Use It

Formulas & functions · Updated November 22, 2026

The IFERROR function in Excel returns a custom result when a formula produces an error, and the formula’s normal result when there is no error. Its syntax is =IFERROR(value, value_if_error), where value is the formula or expression to check and value_if_error is what to show if that expression returns an error like #N/A, #DIV/0!, or #VALUE!.

What Is the IFERROR Function and Why Does It Exist?

IFERROR was introduced in Excel 2007 to simplify error handling. Before that, you had to combine the IF function with ISERROR or ISNA, writing long formulas like =IF(ISERROR(A1/B1),"",A1/B1). IFERROR wraps that logic into one clean function. It catches all Excel error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. It does not catch errors that are not Excel errors, such as text strings that look like errors.

The function is especially useful in dashboards, reports, and lookup formulas where missing data is common. Instead of seeing an ugly error message, you can show a blank cell, a dash, or a friendly note like “Not found”.

How the IFERROR Formula Works: Syntax and Arguments

IFERROR takes exactly two arguments, both required:

  • value – the formula, cell reference, or expression you want to test. If this evaluates without an error, that result is returned.
  • value_if_error – what to return if the first argument produces any error. This can be a number, text in quotes, a cell reference, or another formula.

For example, with the number 10 in cell A1 and 0 in cell B1, the formula =IFERROR(A1/B1,"Cannot divide by zero") returns the text “Cannot divide by zero” because dividing by zero produces #DIV/0!. If B1 contained 2, the same formula would return 5.

Note that IFERROR only checks the first argument. If the value_if_error itself contains an error, that error will still display. Also, IFERROR does not hide errors that come from typing mistakes in the formula itself, like a misspelled function name – those are syntax errors that Excel flags before the formula runs.

Practical Examples of IFERROR in Common Formulas

Here are three typical uses with the exact cell layout assumed:

  • VLOOKUP with missing values: With product IDs in A2:A10 and prices in B2:B10, and a lookup ID in D2, use =IFERROR(VLOOKUP(D2,A2:B10,2,FALSE),"Not in list"). If D2 contains an ID that isn’t in A2:A10, the formula shows “Not in list” instead of #N/A.
  • Division with blanks: With sales in C2 and target in D2, use =IFERROR(C2/D2,0). If D2 is blank or zero, the result is 0 instead of #DIV/0!.
  • Array formula (older Excel): In Excel 2019 and earlier, to sum values from a filtered range, you might use =IFERROR(SUM(1/COUNTIF(A2:A20,A2:A20)),0) entered with Ctrl+Shift+Enter. In Microsoft 365, SUM and COUNTIF handle arrays natively, so this is rarely needed.

Each example returns a clean value instead of an error, making the spreadsheet more readable and less confusing for others.

IFERROR vs IF with ISERROR: Which Should You Use?

IFERROR is shorter and easier to read, but it has a limitation: it catches every error type. Sometimes you only want to catch #N/A (from lookups) and let other errors, like #DIV/0!, show so you can fix them. In that case, use the older combination: =IF(ISNA(VLOOKUP(D2,A2:B10,2,FALSE)),"Not found",VLOOKUP(D2,A2:B10,2,FALSE)). This only catches #N/A. The trade-off is that you repeat the lookup formula twice, which can slow down large workbooks and makes editing harder.

For most users, IFERROR is the better choice because it is concise and handles all common errors. If you need to distinguish between error types, consider the newer IFNA function (available since Excel 2013) which only catches #N/A and is simpler than the ISNA version: =IFNA(VLOOKUP(D2,A2:B10,2,FALSE),"Not found").

Common Problems and Mistakes with IFERROR

  • Hiding real errors: IFERROR can mask bugs. If a formula returns #VALUE! because you accidentally multiplied text, IFERROR will show your custom message and you might not notice the problem. It’s wise to use IFERROR only where you expect errors, not as a blanket wrapper.
  • Returning text that looks like a number: If you use =IFERROR(A1/B1,"0"), the result is text, not a number. This breaks further calculations like SUM or AVERAGE. Use =IFERROR(A1/B1,0) without quotes to keep it numeric.
  • Forgetting to close parentheses: IFERROR requires two arguments and two closing parentheses. A common typo is =IFERROR(A1/B1,"Error" which Excel will flag as a missing parenthesis.
  • Using IFERROR on a range: In older Excel versions, you cannot wrap IFERROR around a formula that returns an array unless you enter it as an array formula. In Microsoft 365, dynamic arrays handle this automatically, but in Excel 2019 you may need Ctrl+Shift+Enter.

IFERROR in Excel for Mac and Excel for the Web

IFERROR works identically in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. The syntax and behavior are the same. The only difference is that on Mac, you might use Command+Return instead of Ctrl+Shift+Enter when entering array formulas in older versions. In Excel for the web, dynamic arrays are supported, so IFERROR works naturally with spilled results.

Alternatives to IFERROR: IFNA, ISERROR, and Error Checking

Besides IFNA, you have other options:

  • IFNA – catches only #N/A, useful for lookups where other errors should remain visible.
  • ISERROR – returns TRUE or FALSE, used inside IF for more complex logic, but requires repeating the formula.
  • Excel’s built-in error checking – go to Formulas > Error Checking to trace and fix errors manually. This doesn’t replace IFERROR but helps you find the source.
  • Conditional formatting – you can highlight error cells using Home > Conditional Formatting > New Rule > Use a formula, with the formula =ISERROR(A1). This doesn’t hide errors but makes them visible.

For most purposes, IFERROR is the simplest and most direct solution. Use IFNA when you specifically want to ignore only #N/A, and use ISERROR when you need to combine error checking with other conditions.

Frequently Asked Questions

Does IFERROR work in Google Sheets?

Yes, Google Sheets has an identical IFERROR function with the same syntax. The main difference is that in Google Sheets, you might see #REF! errors when a formula references a deleted row, and IFERROR will catch those too. The behavior is otherwise the same.

Can IFERROR return a blank cell?

Yes, use empty quotes as the second argument: =IFERROR(A1/B1,""). This returns an empty string, which looks blank. Note that the cell still contains a formula, so it’s not truly empty – functions like COUNTBLANK will not count it, but ISBLANK will return FALSE.

Why does IFERROR show 0 instead of my custom message?

If you wrote =IFERROR(A1/B1,0), the 0 is a number, not text. If you want to show the word “Error”, use quotes: =IFERROR(A1/B1,"Error"). Also check that your value_if_error isn’t a cell reference that contains 0.

Can I use IFERROR with XLOOKUP?

Yes, XLOOKUP has its own built-in error handling with the ‘if_not_found’ argument, so you often don’t need IFERROR. For example, =XLOOKUP(D2,A2:A10,B2:B10,"Not found") returns “Not found” when D2 isn’t in the lookup range. However, you can still wrap XLOOKUP in IFERROR if you want to handle other errors like #VALUE!.

Does IFERROR slow down my spreadsheet?

IFERROR itself adds minimal overhead. The bigger performance impact comes from repeating complex formulas, like using VLOOKUP twice inside IFERROR. To avoid this, use LET (Microsoft 365) to define the lookup once: =LET(x,VLOOKUP(D2,A2:B10,2,FALSE),IFERROR(x,"Not found")). This calculates VLOOKUP only once.

How do I use IFERROR with a cell that contains a formula?

You can wrap any formula in IFERROR, including one that references another cell. For example, if C1 contains =A1/B1, you can write =IFERROR(C1,"Error") in D1. This checks the result of C1 and shows “Error” if C1 evaluates to an error.

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.