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

In Google Sheets, use IFERROR to show a useful alternative whenever a formula returns an error. For example, with a quantity in A2 and a divisor in B2, enter =IFERROR(A2/B2,"Check inputs"); if B2 is zero or the calculation otherwise errors, the cell displays Check inputs instead of the error.

What IFERROR Does in Google Sheets

IFERROR checks the result of an expression or formula. If that result is not an error, Sheets displays it as usual. If it is an error, Sheets returns the alternative value you provide. The function takes a required value to check and an optional fallback value. If you leave out the fallback, Sheets returns a blank when the checked value errors.

This is useful for making reports easier to read, but it does not repair the underlying calculation. A fallback changes what appears in the cell; the source of the error may still need attention. Treat IFERROR as a way to handle an expected outcome, not as a general instruction to conceal every formula problem.

Google Sheets can show errors such as #DIV/0! for division by zero, #N/A when a lookup cannot find a match, #VALUE! when an operation gets an unsuitable value, and #REF! when a formula refers to an invalid cell or range. A formula with invalid syntax may instead show a parse error. Understanding which kind of problem you have helps you choose an appropriate fallback.

A Basic Division Example

Suppose a sales sheet has revenue in B2 and units sold in C2, and you want average revenue per unit in D2. A zero or empty unit count can make the division unusable. With revenue in B2 and units in C2, enter this in D2:

=IFERROR(B2/C2,"No units recorded")

If B2 contains 120 and C2 contains 4, the result is 30. If C2 contains 0, the result is No units recorded. The message is more informative than a bare #DIV/0!, particularly in a shared report. If a blank result is preferable, use an empty text value as the fallback; that displays nothing, though it is not the same thing as leaving the cell genuinely empty.

For a reusable column, enter the formula in the first result row and fill it down using the fill handle or copy and paste. Each row reference adjusts as the formula moves. If you instead want an explicit message for zero but want other errors to remain visible, use a more targeted formula or validate the input separately rather than catching every possible error.

Using IFERROR with Lookups

A common use is replacing a lookup error with a human-readable message. For instance, a product code in E2 might be searched in a two-column table, with codes in A2:A10 and product names in B2:B10. With those ranges and the lookup code in E2, enter:

=IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")

If E2 matches a code in the first column, the formula returns the corresponding name from the second column. If no match exists, it displays Not found. The final FALSE asks VLOOKUP for an exact match, which is generally appropriate for product codes and other identifiers. Check that the lookup values and table values use compatible formats; for example, a number stored as text may not match a numeric value as expected.

The catch is that IFERROR also replaces errors unrelated to a missing match. A misspelled range, an invalid column index, or a damaged reference could also result in the fallback, making the sheet look as though the code simply was not found. If the intended exception is specifically a missing lookup result, a narrower handler is often better.

IFERROR or IFNA?

IFNA is the more specific option when you want to handle only #N/A errors, which commonly occur when a lookup has no match. It leaves other types of errors visible. Using the same lookup table and E2 key described above, this formula returns the matching product name or Not found if the result is #N/A:

=IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found")

Choose between the functions based on what you want to treat as an expected outcome:

Use IFERROR when several kinds of calculation errors should receive the same fallback, such as a dashboard that must display a consistent placeholder.
Use IFNA when a missing match is expected but other problems should remain visible for investigation.
Use neither as a substitute for checking input data or correcting a broken reference.

For example, if a lookup should find a required employee ID, returning Not found may help a user spot a missing record. If it should always find one, leaving #N/A visible may be more useful because it signals that the data needs investigation. The best choice depends on whether the error represents a normal case or a fault.

Choosing a Useful Fallback

A fallback should tell the reader what the result means. A label such as Not found is clearer than a blank when a lookup misses. For a calculation, Check denominator or Enter a valid date may be more helpful than a generic Error. If the result feeds a chart or summary, a blank can keep the display uncluttered, but consider whether it could be mistaken for missing data.

With a value in A2 and a divisor in B2, this version displays an empty string if the division errors:

=IFERROR(A2/B2,"")

The cell looks blank, but it contains a formula that returns text of zero length. That can behave differently from a truly empty cell in later calculations, tests, or exports. For example, a later formula may distinguish between an empty cell and a formula-generated empty string. If downstream logic matters, document what the blank represents or use a clear label instead.

A fallback can also be a number or a cell reference, not only text. But a numeric fallback such as zero can distort totals or averages by treating an error as a real measurement. Use zero only when zero is a valid and defensible substitute for the failed result.

Common Problems and Troubleshooting

When IFERROR does not behave as expected, inspect the original formula before changing the fallback. Click the cell, read the formula bar, and check the displayed error. Then test the calculation without IFERROR in a spare cell or temporarily remove the wrapper. This reveals whether the issue is a bad input, a missing match, or a broken reference.

The formula still shows a parse error: IFERROR cannot catch a syntax problem in the formula itself because Sheets must parse the formula before it can calculate it. Check parentheses, quotation marks, separators, and function names.
The result is blank unexpectedly: A blank fallback may be hiding a genuine error. Temporarily replace it with a descriptive message to identify what is happening.
A lookup says Not found despite a visible match: Check for extra spaces, mismatched number-versus-text formats, and whether the lookup column is the first column of the VLOOKUP range.
A broken reference appears hidden: IFERROR may replace #REF! with the same message used for an ordinary missing value. Review the source formula and range rather than assuming the fallback confirms the data is valid.

Some Google Sheets locales use semicolons rather than commas as formula argument separators. If a formula copied from a comma-using example is rejected, check File > Settings for the spreadsheet’s locale and use the separator expected by that locale. The examples here use commas. Excel also has IFERROR with the same general purpose and argument pattern, although spreadsheet locale settings can affect formula separators there too.

When Not to Use IFERROR

A blanket IFERROR around a complex formula can make a polished-looking sheet less trustworthy. For example, if a monthly report turns every error into zero, a broken source range can quietly make the report appear to show no activity. A reader may not know that the displayed zero is a fallback rather than a calculated result.

Before adding a wrapper, ask whether the error is expected and what the replacement communicates. If missing input is normal, a message or blank may be appropriate. If the error signals a mistake that someone should fix, let it remain visible or handle only the expected error type. You can also add data validation or input checks to prevent common causes, such as an invalid entry in a required field.

IFERROR also cannot make an invalid formula meaningful. It does not fix a wrong range, make an approximate lookup exact, or ensure that a returned value is sensible. A successful formula can still produce the wrong answer without generating any error. Check assumptions and sample results even when the cell displays no error.

Frequently Asked Questions

What does IFERROR do in Google Sheets?
It returns a formula’s result when that result is not an error, and returns a fallback value when it is. The fallback can be text, a number, a reference, or an empty string.

How do I show a blank instead of an error?
Wrap the calculation in IFERROR and use two quotation marks as the fallback, such as the division example above. Remember that the cell then contains a formula returning an empty string, not a genuinely empty cell.

Does IFERROR catch #N/A?
Yes. It catches #N/A as well as other formula errors. If you want to handle only #N/A and leave other errors visible, use IFNA instead.

Can IFERROR fix a formula parse error?
No. Parse errors occur before Sheets can evaluate the formula. Check the formula’s syntax, separators, quotation marks, and parentheses.

Why does IFERROR hide an error I need to see?
Its fallback applies to any error returned by the expression, not just the one you had in mind. Replace a blank or generic fallback temporarily with a diagnostic message, inspect the underlying formula, and consider using IFNA for missing-match cases.

Practical Takeaway

Use IFERROR when you know which error conditions are acceptable and have chosen a fallback that accurately describes them. Use IFNA for lookup cases where only a missing result should be handled. Keep unexpected errors visible, especially in calculations that influence totals, reports, or decisions. That balance makes Google Sheets easier to read without hiding problems that deserve correction.

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.