IFERROR in Google Sheets: Meaning and How to Use It

In Google Sheets, IFERROR checks whether a formula produces an error and lets you show a different value if it does. Its pattern is =IFERROR(value, value_if_error): Sheets returns the first value when it is not an error, and the fallback when it is. For example, it can replace a division-by-zero error with a message or a blank cell.

Is IFERROR a word or a function?

IFERROR is the name of a spreadsheet function, not a standard English word or a slang term. It is a technical identifier formed from the ordinary words “if” and “error”: the function asks, in effect, “if this calculation has an error, what should be returned?” The name describes its purpose rather than having a separate dictionary meaning.

Google Sheets recognizes IFERROR as a built-in function. You may see it written in uppercase in examples, but the function name is not a word that needs capitalization in ordinary writing. In a sentence, a natural usage would be: “I used IFERROR to show ‘No match’ instead of an error when the lookup found no result.” In a spreadsheet cell, you use it as part of a formula, not as a label or a command typed by itself.

What IFERROR returns

IFERROR evaluates its first argument. If that argument produces a value without an error, the function returns that value unchanged. If it produces an error, IFERROR returns the fallback supplied as its second argument. That fallback can be text, a number, a blank-looking empty string, or another calculation.

It can handle common formula errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. The error must come from the value being evaluated; IFERROR does not repair the underlying calculation or correct bad source data. It changes what the formula returns.

If you omit the optional second argument, Google Sheets returns a blank when the first argument is an error. That can be convenient in a display-oriented report, but it may make a broken calculation less noticeable. Supplying an explicit message or a deliberate fallback value often makes a sheet easier to audit.

Syntax and where to enter the formula

The function takes a value to test and, optionally, a value to return if the first one is an error. The first argument is often another formula, but it can also be a cell reference. The second argument is optional; when included, separate the arguments with a comma in a spreadsheet using a comma-based locale.

For the syntax example below, assume the formula is entered in C2, A2 holds the value or expression being checked, and B2 holds a fallback value. The words in the pattern describe argument positions; they are not literal cell references to type.

=IFERROR(value, value_if_error)

In Google Sheets in a web browser, select the cell where the result should appear, type the formula beginning with an equals sign, and press Enter. You can also begin typing =IFERROR( and use the function suggestions that appear. If the spreadsheet uses a locale with different formula separators, its required argument separator may differ; check the sheet’s locale under File > Settings. Excel also has an IFERROR function, but this article’s instructions and examples are for Google Sheets.

Basic example: replace a division error

Suppose A2 contains 10 and B2 contains 0. Dividing A2 by B2 produces #DIV/0!, because division by zero is undefined. To display a message in C2 instead, use this formula:

With 10 in A2 and 0 in B2, enter this in C2. The result is the text “Not available.”

=IFERROR(A2/B2,"Not available")

If B2 changes to 2, the division succeeds, so C2 displays 5 rather than the fallback. This is an important part of the function’s behavior: the second argument is not a replacement for every result. It is used only when the first argument evaluates to an error.

You can return a number instead of text. With a numerator of 120 in A2 and a zero denominator in B2, enter this in C2; it returns 0. If B2 is 4, it returns 30.

=IFERROR(A2/B2,0)

Use a numeric fallback only when that number has a sound meaning in the calculation. Replacing an invalid result with zero may make a chart or total look complete even though the source calculation failed.

Common examples: lookups and empty displays

A common use is handling a lookup that does not find a matching item. VLOOKUP returns #N/A when it cannot find a match in its search range. IFERROR can replace that error with a clearer message.

Assume product names are in A2:A10, prices are in B2:B10, and the requested product name is in E2. Enter this in F2. If the exact product name is found, the formula returns its price; if it is not found, it returns “No match.”

=IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"No match")

In this example, FALSE asks VLOOKUP for an exact match. IFERROR also catches errors other than “not found,” however. If the lookup range is broken or the formula has an invalid reference, the same message may appear. That broad coverage can conceal a problem that deserves attention.

To leave a cell visually empty when a calculation fails, use an empty text string as the fallback. With A2 holding a number and B2 holding zero, enter this in C2; the displayed result is blank.

=IFERROR(A2/B2,"")

The formula still exists, and its result is an empty string; it does not delete the formula or make the underlying error disappear. A genuinely empty cell and a cell whose formula returns an empty string can behave differently in some calculations, so use this option mainly when a clean-looking display is useful.

Using IFERROR with a range

IFERROR can also be used with array calculations in Google Sheets. When a calculation produces a set of results, the fallback can be applied to error results within that output. This is useful for columns where some rows have valid inputs and others do not. Check the resulting spill area to ensure there is room for the formula’s output.

Assume A2:A5 contains 8, 6, 9 and 4, while B2:B5 contains 2, 0, 3 and 0. Enter this formula in C2. The output is 4, a blank, 3 and a blank in C2:C5, respectively.

=ARRAYFORMULA(IFERROR(A2:A5/B2:B5,""))

Here, the two division-by-zero errors are replaced with empty strings, while the valid divisions remain visible. If you want errors to be obvious during data checking, leave them visible or use a descriptive fallback rather than hiding them. Also remember that an array formula can occupy multiple cells; existing content in its output range can prevent the results from expanding.

IFERROR versus IFNA

IFERROR catches errors broadly. IFNA is narrower: it supplies a fallback only when the first argument returns #N/A. That distinction matters for lookups. A missing lookup match commonly produces #N/A, so IFNA can handle the expected “not found” case while allowing other errors, such as #REF!, to remain visible.

Assume E2 contains a product to find, product names are in A2:A10, and prices are in B2:B10. Enter this lookup formula in F2. It returns the matching price or “No match” for an #N/A result; other kinds of errors are not replaced by that fallback.

=IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"No match")

Choose IFERROR when any error in the tested expression should receive the same treatment. Choose IFNA when only a missing-value result is expected and other errors should remain visible for troubleshooting. Neither function fixes a formula; both change its returned result under specified conditions.

Common problems and sensible use

A frequent mistake is wrapping a large formula in IFERROR and assuming the result is therefore correct. A fallback can make a report look tidy while concealing an incorrect reference, an unexpected text value, or a broken lookup range. Before using IFERROR, test the underlying expression on its own and decide which errors are expected.

Do not hide errors during investigation. Temporarily remove the wrapper or use a clear fallback so you can identify the cause.
Choose a fallback that fits the data. Text such as “No match” is informative for a lookup; zero is appropriate only when zero accurately represents the intended result.
Watch for type changes. A formula may normally return numbers but return text such as “Not available” on error. That mixed output can affect later arithmetic, sorting, or charting.
Check every error path. IFERROR does not guarantee that the fallback itself is valid. If the fallback expression produces an error, that error can still appear.
Keep the cause visible where needed. In a working or audited spreadsheet, an explicit message is often safer than a blank or zero.

IFERROR is most useful when an error is an expected outcome and a specific alternate display is appropriate—for example, a division where the denominator may be zero or a lookup where a record may be absent. It is less suitable as a blanket layer around every formula. A clean report matters, but so does retaining a way to notice unexpected data and formula problems.

Frequently Asked Questions

What does IFERROR do in Google Sheets?

It returns the result of its first argument when that result is not an error. If it is an error, IFERROR returns the alternate value supplied as the second argument, or a blank if that argument is omitted.

How do I use IFERROR to show a blank?

Put an empty text string, written as two double quotes with nothing between them, in the fallback position. For example, a division formula can use =IFERROR(A2/B2,""). The cell displays blank on error, but the formula remains in the cell.

Does IFERROR fix the error in a formula?

No. It changes the result shown when the tested expression errors; it does not correct the calculation, repair a reference, or change the source data. Investigate the cause when the error is unexpected.

Should I use IFERROR or IFNA for a lookup?

Use IFNA when you only want to replace a missing-match #N/A result and want other errors to remain visible. Use IFERROR if every error from the lookup expression should receive the same fallback.

Can IFERROR return zero instead of an error?

Yes. Put 0 in the second argument. Do this only if zero is a meaningful substitute in your sheet, because it can otherwise make a failed calculation appear to be a genuine zero result.

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.