IFERROR Formula in Google Sheets: Syntax and Examples

In Google Sheets, use IFERROR to show a chosen fallback instead of an error: if the calculation is in A2 and you want a blank when it fails, enter =IFERROR(A2, ""). IFERROR returns the calculation’s result when it succeeds and the fallback when it produces an error.

What IFERROR Does

Spreadsheet errors are useful signals: they can reveal a division by zero, a missing lookup result, an invalid reference, or a value of the wrong type. But an error displayed in a report or dashboard may be confusing or distracting. IFERROR lets you decide what to display when a particular expression fails, without changing the underlying data or correcting the source of the error.

The function has two arguments: a value or expression to evaluate, and an optional value to return if the first argument produces an error. The fallback can be a number, text, a blank string, or another expression. If you leave the fallback out, Google Sheets returns a blank when the first argument errors. If the first argument is valid, IFERROR returns its result as it normally would, including a legitimate zero or blank.

IFERROR can handle errors such as #DIV/0!, #N/A, #VALUE!, #REF!, and #NAME?. It does not mean “return the fallback if the cell is empty,” and it does not treat every unusual result as an error. A zero, an empty cell, or text in a cell is not automatically an error.

How to Use IFERROR in a Formula

Put the expression you want to protect first, followed by the fallback, separated by a comma. The examples below use commas, as in a typical Google Sheets locale. Depending on your spreadsheet’s locale, the formula separator may be a semicolon instead.

With revenue in A2 and units sold in B2, use this formula in C2 to calculate revenue per unit and show a message if the calculation fails: =IFERROR(A2/B2, "No units"). If A2 contains 120 and B2 contains 4, the result is 30. If B2 is zero, the division produces #DIV/0!, so C2 displays No units instead.

The fallback should communicate what happened in a way that makes sense to the people using the sheet. You might use a blank for a polished report, a short label for a data-entry table, or a numeric value only when treating the error as that number is appropriate. The choice changes what readers see, so it should not be made solely to make the sheet look tidy.

To apply the formula to additional rows, enter it in the first result cell and use the fill handle to copy it down. Google Sheets adjusts relative references: in the next row, A2 and B2 become A3 and B3. Check a few filled rows to make sure the references still point to the intended cells.

Common IFERROR Examples

Division is a frequent use because a blank or zero denominator can cause an error. With amounts in A2 and a divisor in B2, enter this formula in C2 to leave the result blank if division fails: =IFERROR(A2/B2, ""). If the calculation is valid, the result remains visible; if it errors, the cell appears empty. The empty quotation marks create an empty text result, which is not quite the same as a truly empty cell.

IFERROR is also useful around lookups. With product IDs in A2:A100, product names in B2:B100, and the ID to search for in D2, use this formula in E2 to display a message when an exact match is unavailable: =IFERROR(VLOOKUP(D2, A2:B100, 2, FALSE), "Not found"). The final FALSE asks VLOOKUP for an exact match. If D2 matches an ID in the first column of the lookup range, the formula returns the corresponding name from the second column. If it does not, the formula displays Not found.

A lookup can fail for reasons other than a genuinely missing ID, such as an incorrect range or a damaged reference. IFERROR hides all those errors in the same way. If it matters to distinguish “not found” from a broken formula, use a more targeted approach or investigate the error rather than relying on a generic message.

With scores in B2:B10, use this formula in C2 if you want a message when there are no numeric values to average: =IFERROR(AVERAGE(B2:B10), "No scores"). AVERAGE can produce an error when it has no numbers to average; IFERROR replaces that error with the specified text. If the range contains valid numbers, the formula returns their average as usual.

Choosing a Useful Fallback

A fallback is part of the meaning of the result, not just a formatting trick. Consider whether users should see that something went wrong, whether a substitute value is safe, and whether downstream formulas expect a number or text. A message such as No units makes a calculation problem visible; a blank can make a report cleaner, but it can also make missing results easy to overlook.

Use a short message when a person needs to know why a result is missing, such as Not found or Check input.
Use a blank string when an error should leave a presentation cell visually empty and the omission will not be mistaken for a real result.
Use a numeric fallback only when that number is a defensible substitute in the calculation. Replacing an error with zero can affect totals, averages, and decisions.
Keep types consistent when other formulas use the result. A text fallback in a column of numbers may behave differently from a numeric value in later calculations.

For example, turning a failed sales calculation into zero says something stronger than “the result could not be calculated”: it says the value is zero. If the cause is actually a missing input, that substitution may understate a total. When data quality matters, a visible message or an explicit blank may be more honest than a number.

IFERROR Compared with IFNA and IF

IFERROR catches any error produced by its first argument. That broad coverage is convenient, but it can conceal different problems behind one fallback. IFNA is narrower: it handles #N/A errors, which commonly occur when a lookup has no match, while allowing other errors to remain visible.

With a lookup ID in D2, IDs in A2:A100, and names in B2:B100, use this formula in E2 if you want to replace only a missing-match result: =IFNA(VLOOKUP(D2, A2:B100, 2, FALSE), "Not found"). A reference problem or another type of error will not be converted to Not found, making it easier to notice that the formula itself may need attention.

IF can check a condition you specify, such as whether a divisor is zero, rather than responding to every error. With a numerator in A2 and a denominator in B2, enter this formula in C2 to handle a zero denominator explicitly: =IF(B2=0, "No units", A2/B2). This makes the intended condition clear, but it does not catch unrelated errors in the calculation. Choose IF when you know the condition to test, IFNA when only #N/A should be handled, and IFERROR when any error should use the same fallback.

Common Problems and Misconceptions

IFERROR hides the cause, not the cause itself. If a formula refers to the wrong cell, the reference stays wrong even though the displayed error is replaced. During troubleshooting, temporarily remove the wrapper or use a fallback message that identifies the problem. Once the expression is verified, decide whether hiding its errors is appropriate.

A blank-looking cell may not be empty. A formula that returns an empty string still contains a formula and produces a text result. Other formulas, filters, or checks that distinguish empty cells from formula results may therefore treat it differently from a cell that has never had a value. Do not assume that a blank-looking IFERROR result is identical to deleting the cell contents.

Text fallbacks can affect later calculations. If a column is meant to contain numbers, a message such as Not available introduces text into that output. Some functions ignore text in numeric ranges while other operations may behave differently or return errors. Consider keeping the raw calculation in one column and the reader-friendly display in another when the output must also support further analysis.

Not every unexpected result triggers IFERROR. A lookup that finds the wrong matching record, an incorrect but valid calculation, or a zero where a positive number was expected may not produce an error at all. IFERROR cannot determine whether a result is logically correct; that requires checking the formula, source data, and business rules.

Check the formula before adding more wrappers. Nested error-handling formulas can make a sheet harder to understand and can hide which step failed. Start with the smallest expression that produces the problem, confirm its inputs and references, and use the narrowest error-handling function that meets the requirement.

Practical Workflow for Reliable Sheets

When adding IFERROR to an existing workbook, first inspect the unwrapped expression and identify the errors that can reasonably occur. Next, choose a fallback that tells the truth about the result. Then test ordinary inputs and likely edge cases: a zero divisor, an empty input, an unmatched lookup, and any boundaries relevant to the calculation. Finally, confirm that totals, charts, and formulas using the output still behave as intended.

For a shared sheet, make the fallback understandable to someone who did not create the formula. A note in a header or a short instruction can explain what a blank or message means. If users need to correct missing data, a visible prompt is often more helpful than silently suppressing every error. If the sheet is primarily a presentation, a cleaner display may be reasonable, provided the underlying data can still be audited.

Google Sheets formulas can be edited in the browser by selecting the cell and using the formula bar or typing directly into the cell. IFERROR itself does not require a special menu command or setting. As with other spreadsheet functions, you can copy or fill a formula, but review the references after copying, especially when a lookup range should remain fixed. Add dollar signs to references only when you need them to stay fixed as the formula is copied.

Frequently Asked Questions

What is the IFERROR formula in Google Sheets?
IFERROR evaluates a value or expression and returns a chosen fallback if that expression produces an error. Its second argument is optional; if omitted, an error produces a blank result.

How do I make IFERROR return a blank?
Put an empty text value as the fallback. With the calculation in A2, use =IFERROR(A2, ""). The result looks blank if A2 errors, but the cell still contains a formula.

Does IFERROR hide #N/A in Google Sheets?
Yes. IFERROR handles #N/A along with other errors. If you only want to replace #N/A and leave other errors visible, use IFNA instead.

Why is my IFERROR formula still showing an error?
The first argument may not be the source of the displayed error, the fallback may itself produce an error, or the formula may have a syntax or separator issue. Check the formula and locale separator, then test the expression without IFERROR to see the original error.

Does IFERROR fix a broken formula?
No. It changes what is displayed when an expression errors; it does not repair incorrect references, missing data, or faulty logic. Use it to manage expected errors after checking that the underlying formula is sound.

Is IFERROR the same in Excel?
The basic IFERROR function is available in both Google Sheets and modern Excel, and its purpose is similar. Interface details and some locale settings differ, so instructions for menus or regional separators may not transfer exactly between the programs.

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.