IFERROR Function in Google Sheets

If you want to catch errors in a formula and show something friendly instead of a scary #DIV/0! or #N/A, the IFERROR function in Google Sheets is what you need.

It runs your formula, and if the result is an error, it swaps in whatever fallback you choose. In this article, I’ll show you how to use IFERROR with simple division, lookups, blank fallbacks, and nested lookups.

IFERROR Function Syntax in Google Sheets

Here is how the IFERROR function is written:

=IFERROR(value, [value_if_error])

• value is the formula or expression you want to evaluate.
• value_if_error is what to return if value turns out to be an error. This argument is optional. If you leave it out, IFERROR returns an empty string when there’s an error.

When to Use IFERROR Function

• Replace division errors when a denominator might be zero or blank.
• Show a clean “Not found” label instead of #N/A from a failed lookup.
• Hide errors entirely by returning an empty cell.
• Convert text that won’t parse into a number into a safe fallback value.
• Chain two lookups so the second one runs only if the first one fails.

Example 1: Catch a Division Error

Let’s start with the most common case, dividing one number by another.

Below is the dataset, with the numerator in column A and the denominator in column B, across rows 2 to 6. One of the denominators is zero.

You want to divide A by B for each row, but show “n/a” wherever the division would fail.

Here is the formula:

=IFERROR(A2/B2, "n/a")

For every row where the math works, IFERROR passes the division result straight through. The first row gives 5, and the others follow the same way.

The row dividing by zero would normally throw #DIV/0!. Instead, IFERROR catches it and the cell shows n/a.

Pro Tip: IFERROR only reacts to errors. If a row evaluates to a normal value like zero or a blank, that value passes through untouched. It does not get replaced by the fallback.

Example 2: Friendly Fallback for a Failed Lookup

Lookups are the place where IFERROR earns its keep, since a missing key returns #N/A.

Below is the dataset, with a product ID in column A, a product name in column B, and a list of IDs to look up in column C, across rows 2 to 5.

You want to look up each ID in column C and return its name, but show “Not found” when the ID isn’t in the table. The same pattern works with the HLOOKUP function when your table runs across rows.

Here is the formula:

=IFERROR(VLOOKUP(C2, A:B, 2, FALSE), "Not found")

When the VLOOKUP finds a match, IFERROR lets the name through, so the first row becomes Banana.

When the ID isn’t in the table, VLOOKUP would return #N/A. IFERROR catches that and the row becomes Not found instead.

Example 3: Return a Blank Instead of an Error

Sometimes you don’t want any label at all. You just want the error to disappear.

Below is the dataset, with a numerator in column A and a denominator in column B, across rows 2 to 5. One denominator is zero again.

You want the division result, but a clean empty cell wherever the math fails.

Here is the formula:

=IFERROR(A2/B2, "")

The empty quotes "" are the fallback. Rows that divide fine show their number, and the first row gives 3.

The row dividing by zero becomes a blank cell. This keeps your sheet looking tidy when you plan to chart or sum the column later.

Pro Tip: A cell that holds “” looks empty but isn’t truly blank. Functions like ISBLANK will treat it as filled, so keep that in mind if you check the column later.

Example 4: Safely Convert Text to a Number

Mixed columns where some cells are text and some are numbers can break your math. IFERROR plus VALUE smooths that over.

Below is the dataset, with values in column A, across rows 2 to 6. Some entries are real numbers and some are text that won’t convert.

You want each entry as a number, with anything that won’t convert treated as zero.

Here is the formula:

=IFERROR(VALUE(A2), 0)

VALUE tries to turn each entry into a number. When the entry is a clean numeric string, it converts and the first row gives 100.

When VALUE hits text it can’t parse, it throws an error. IFERROR catches it and drops in 0, so the whole column stays numeric and ready to total.

Example 5: Chain Two Lookups With Nested IFERROR

Here’s the trick that surprises people. You can nest IFERROR inside another IFERROR to try a second source when the first one comes up empty.

Below is the dataset, with one lookup table in columns A and B, a second table in columns C and D, and the IDs to search in column E, across rows 2 to 5.

You want to look up each ID in the first table, fall back to the second table if it isn’t there, and show “Missing” only if both tables fail.

Here is the formula:

=IFERROR(VLOOKUP(E2, A:B, 2, FALSE), IFERROR(VLOOKUP(E2, C:D, 2, FALSE), "Missing"))

How this formula works:

• The outer IFERROR runs the first VLOOKUP against columns A and B.
• If that match is found, its name passes through.
• If the first VLOOKUP errors, the outer IFERROR runs the second VLOOKUP against columns C and D.
• If the second lookup also fails, the inner IFERROR returns Missing.

So the row that exists only in the second table still resolves, while the row in neither table lands on Missing.

Tips & Common Mistakes

• IFERROR hides every error type, not just #N/A. That includes typos and broken references like #REF!, so a wrong formula can look “fixed” when it’s actually still wrong. Test your formula without IFERROR first, then wrap it.
• The fallback runs only on errors. A legitimate zero, FALSE, or blank result is not an error, so IFERROR leaves it alone. Don’t expect it to replace those.
• Wrap the whole expression, not just one piece. =IFERROR(A2/B2, 0) is right. Putting IFERROR around only A2 won’t catch the division error.

IFERROR is the cleanest way to keep error messages out of a finished sheet. Wrap the risky part, pick a sensible fallback, and your columns stay readable.

Start with a plain label like “n/a” or “Not found”, and reach for nested IFERROR only when you genuinely have a second source to try.

List of All Google Sheets Functions

Related Google Sheets Functions / Articles:

• IFNA Function in Google Sheets
• ISNA Function in Google Sheets
• IF Function in Google Sheets
• XLOOKUP Function in Google Sheets
• IFS Function in Google Sheets
• ISNUMBER Function in Google Sheets