Google Sheets IFERROR: How It Works and When to Use It

In Google Sheets, IFERROR returns a chosen fallback when a formula produces an error; otherwise, it returns the formula’s normal result. For example, use it to show a blank instead of a divide-by-zero error or a helpful message when a lookup cannot find a match.

What IFERROR does

IFERROR is a worksheet function that checks the result of an expression. If that expression evaluates without an error, Sheets displays the result as usual. If it evaluates to an error, the function returns the fallback you specify. This makes a formula’s output easier to read, especially in reports or tables that are shared with other people.

It can handle errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, and #NUM! when they arise during calculation. It does not fix the cause of an error; it changes what the cell displays when the calculation returns one. That distinction matters: a missing lookup result may be expected, while a broken cell reference may point to a problem that deserves attention.

IFERROR is not a setting or a menu command. You enter it as part of a cell formula in Google Sheets, which you can use in a web browser. It works with other formulas, operators, and functions, so the expression being checked can be as simple as a division or as complex as a nested lookup.

Syntax and arguments

The general syntax is IFERROR(value, value_if_error). The first argument, value, is the expression Sheets evaluates. The second, value_if_error, is what to return if that expression produces an error. The second argument is optional; if you leave it out, Sheets returns a blank when the first argument errors.

For instance, with the numerator in A2 and the denominator in B2, this returns the division result unless the operation errors, in which case it displays the text “Check value”:

With the numerator in A2 and the denominator in B2: =IFERROR(A2/B2,"Check value")

The fallback can be text, a number, a cell reference, or another expression. It should make sense in the context of the expected result. A message such as “Not found” may be appropriate for a lookup, while a blank may suit an optional calculation. Returning a number such as zero can be misleading if readers interpret it as a genuine measured value.

Practical examples

A common use is suppressing division errors when a denominator is zero or empty. Suppose column A contains units sold and column B contains the corresponding cost, with the calculation in C2. With the numerator in A2 and the denominator in B2, this displays a blank instead of an error:

With units in A2 and cost in B2: =IFERROR(A2/B2,"")

The empty text between the quotation marks makes the cell appear blank. If you prefer to explain what happened, use a label instead. With the same layout, this returns “No denominator” when the division fails:

With units in A2 and cost in B2: =IFERROR(A2/B2,"No denominator")

IFERROR is also common with lookups. If E2 contains a product code and A2:B10 contains product codes in the first column and descriptions in the second, this displays “Not found” when the lookup does not return a result:

With product codes in A2:A10, descriptions in B2:B10, and the code to find in E2: =IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")

The final argument, FALSE, asks VLOOKUP for an exact match. IFERROR can also catch other errors from the lookup expression, not just the expected missing-match case. If you want to handle only a missing match, IFNA is usually a more precise choice, as explained below.

Using IFERROR with ranges and arrays

When a calculation is applied to a range, errors can occur in some rows but not others. In Google Sheets, array calculations can return multiple results, and IFERROR can apply a fallback to the error results while preserving successful results. For example, if A2:A5 holds amounts and B2:B5 holds corresponding quantities, you can divide the ranges and use an empty string for rows that produce errors.

With amounts in A2:A5 and quantities in B2:B5: =ARRAYFORMULA(IFERROR(A2:A5/B2:B5,""))

This kind of formula can be useful when a column is expected to calculate several rows at once. Check the results against a few individual rows before relying on it in a report: a blank could mean a zero denominator, an empty input, or another calculation error. Also consider whether an error message would be more informative than a blank for people maintaining the sheet.

For simpler tables, a formula in the first row that is copied down may be easier to audit. Use an array formula when it fits the structure of the sheet and makes updates simpler, not merely to hide errors across a large range.

IFERROR versus IFNA and IF

Choose the error-handling function based on which errors you intend to handle. IFERROR catches any error returned by the expression. IFNA catches only the #N/A error, which commonly indicates that a lookup did not find a match. If your lookup should show a message for a missing item but still expose unrelated problems, IFNA is more targeted.

With product codes in A2:A10, descriptions in B2:B10, and the code to find in E2: =IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")

IF is for testing a condition, such as whether a denominator is zero, rather than catching all calculation errors. With a numerator in A2 and denominator in B2, this displays a blank when B2 is zero and otherwise performs the division:

With a numerator in A2 and denominator in B2: =IF(B2=0,"",A2/B2)

This approach makes the intended condition explicit. It does not necessarily handle every possible error in the division, such as a text value where a number is expected. Use IF when you know the condition to test; use IFERROR when you deliberately want a fallback for errors from an expression. Avoid wrapping everything in IFERROR by habit, because broad error handling can conceal issues that need correction.

Choosing a useful fallback

A fallback is part of the meaning of the result, not just a cosmetic choice. A blank keeps a report uncluttered, but it does not tell a reader why a value is absent. A text message explains more, but can make a column that otherwise contains numbers harder to sort, chart, or use in further calculations. A numeric fallback keeps the column numeric, yet may be mistaken for a real value.

Before choosing, consider who will read or use the result and whether downstream formulas depend on its type. For example, a summary calculation that expects numbers may not handle a text message as intended. Test the output in any charts, filters, totals, and formulas that use the cell. If the difference between “not applicable,” “not entered,” and “calculation failed” matters, a single blank fallback may discard useful information.

A practical rule is to use a targeted message for a visible lookup result, a blank only when an absent value is genuinely acceptable, and a deliberate numeric fallback only when that number has a clear meaning. If the underlying error should be fixed rather than hidden, leave it visible or add a separate check that makes the problem easy to find.

Common problems and misconceptions

It hides more than the expected error. IFERROR catches any error in its first argument. A misspelled function or invalid reference that evaluates as an error may therefore produce the same fallback as a normal “not found” result.
It cannot repair a formula parse error. If Sheets cannot parse the formula itself, the formula may not be evaluated, so wrapping it in IFERROR is not a way to correct missing parentheses, incorrect separators, or other syntax problems.
A blank-looking result is not always an empty cell. A fallback of "" is an empty text value. Functions that count nonempty cells may treat it differently from a truly untouched cell, and later formulas may receive text rather than an empty cell.
Zero and blank are not interchangeable. Zero is a numeric result and may affect averages, totals, and charts; a blank can be excluded or treated differently. Choose based on what the data is supposed to represent.
The source error still exists logically. IFERROR changes the displayed result of the formula, but it does not correct bad source data, restore a deleted reference, or make a failed lookup find a match.

When troubleshooting, temporarily remove the wrapper or inspect the expression inside it. Seeing the original error can reveal whether the problem is an expected missing value or a genuine mistake. Once you understand the cause, decide whether to fix the input, change the calculation, or keep a fallback.

Google Sheets and Excel differences

IFERROR is available in both Google Sheets and modern Microsoft Excel, and its central purpose and two-argument pattern are similar. The examples on this page are for Google Sheets in a web browser; formulas use commas between arguments, as shown in the Google Sheets examples. Excel settings can use different list separators depending on regional settings, so a copied formula may need separators adjusted to the configuration of that Excel installation.

Other spreadsheet behavior, function availability, and array calculation details can differ between programs or versions. If you move a sheet to Excel, verify formulas and results in the destination application rather than assuming every surrounding feature behaves identically. For basic error handling, however, IFERROR is a familiar option in both.

Frequently Asked Questions

What does IFERROR do in Google Sheets?
It returns the normal result if its first argument evaluates successfully, and returns a chosen fallback if that argument produces an error.

How do I make IFERROR show a blank?
Use an empty text string as the second argument. For example, with a calculation in A2 that you want to protect, use =IFERROR(A2,""). This returns the value in A2 unless it errors, and otherwise displays a blank-looking result.

Does IFERROR catch #N/A?
Yes. It catches #N/A and other errors returned by its first argument. If you only want to handle #N/A, use IFNA instead so other errors remain visible.

Why is IFERROR still showing a formula error?
The formula may have a parse or syntax problem that prevents it from being evaluated, or the error may occur outside the expression passed to IFERROR. Check the parentheses, separators, references, and the exact part of the formula being wrapped.

Can IFERROR hide a real spreadsheet problem?
Yes. Because it catches many kinds of errors, a broad fallback can conceal a broken reference or invalid input along with an expected missing result. Use it selectively, and choose IFNA or an explicit IF condition when you only intend to handle one known case.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.