How to Use IFERROR in Google Sheets

Every spreadsheet user has experienced the frustration of a formula gone wrong. You build what you think is a simple calculation, hit Enter, and get a cryptic error code like #N/A or #DIV/0! instead of the result you expected. The error stares back at you, breaking your dashboard layout, making reports look unprofessional, and sometimes causing confusion about what went wrong.

This is where IFERROR comes in. It’s a straightforward function that catches errors in your formulas and replaces them with something clean: a blank cell, a zero, or a custom message. Instead of seeing #N/A in a product lookup, you see “Not found.” Instead of #DIV/0! when dividing by zero, you see nothing. Instead of #REF! when a formula references a deleted cell, you see a helpful label.

IFERROR doesn’t fix the underlying problem, but it handles it gracefully so your sheet stays usable and your reports look professional. This guide covers every scenario where IFERROR saves the day, from beginner-friendly setups to advanced nested formulas that chain multiple lookups together.

IFERROR Syntax Explained

IFERROR has a simple two-part structure that you’ll memorize quickly.

=IFERROR(value, value_if_error)

The first part, “value,” is your main formula or cell reference. This is what you want to calculate. It could be a VLOOKUP formula, a division, a reference to another cell, or anything else that might produce an error.

The second part, “value_if_error,” is what Google Sheets returns if the first part encounters any error. This can be a blank string (empty text “”), the number zero, or any text or value you want to display.

Here’s a real example. Suppose cell A1 contains the number 10 and B1 contains the number 0. The formula =A1/B1 will produce #DIV/0! because you cannot divide by zero. Wrap it with IFERROR:

=IFERROR(A1/B1, 0)

Now if B1 is zero, the formula returns 0 instead of showing the error. If B1 is any other number, the division works normally and you get the result.

Another example: =IFERROR(B2, “Missing”) looks at cell B2. If B2 has a value, it displays that value. If B2 contains an error, it displays the text “Missing” instead.

The Errors IFERROR Catches

IFERROR catches all error types in Google Sheets, but understanding what triggers each one helps you build better formulas and avoid mistakes in the first place.

#N/A appears when a VLOOKUP or HLOOKUP cannot find the lookup value. If you’re searching for product ID “XYZ123” in a table and that ID doesn’t exist, #N/A pops up. This is the most common error you’ll wrap with IFERROR.

#VALUE! means you used the wrong type of data for an operation. For example, if you try to multiply a number by a text string, you get #VALUE!. If a formula expects a date but receives text that doesn’t parse as a date, #VALUE! appears.

#REF! occurs when a formula references a cell that no longer exists. If your formula points to cell E5 and you delete column E, the formula breaks with #REF!. This also happens when you copy a formula into a location where the relative references go out of bounds.

#DIV/0! is the divide-by-zero error. Any time you divide by zero, whether directly with =10/0 or indirectly with a formula that divides by a cell containing zero, you get #DIV/0!.

#NAME? means Google Sheets doesn’t recognize a function name or a named range you referenced. If you misspell VLOOKUP as VLOKUP, you get #NAME?. If you reference a named range that doesn’t exist, same error.

#NUM! occurs when a calculation produces a number that’s too large or too small for Google Sheets to handle, or when you use an invalid argument in a function. For example, some functions require positive numbers, and if you pass them a negative number, #NUM! appears.

#ERROR! is a catchall for other issues that don’t fit the above categories. It’s rare but it happens.

IFERROR catches every single one of these.

IFERROR with VLOOKUP: The Classic Pairing

If there’s one formula combination that defined IFERROR’s popularity, it’s pairing it with VLOOKUP. Read our guide on VLOOKUP in Google Sheets for the full breakdown, but here’s the quick version: VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row.

The problem: if the lookup value doesn’t exist, VLOOKUP returns #N/A. In a sheet with hundreds of rows, columns full of #N/A errors look terrible and confuse users.

The solution: wrap VLOOKUP with IFERROR.

Imagine you have a product database in columns A and B: product ID in column A and product name in column B. In another section of your sheet, you have order data with product IDs in column D. You want to look up the product names and put them in column E.

Your VLOOKUP formula is =VLOOKUP(D2, A:B, 2, FALSE). This looks for the value in D2 within columns A and B, and returns the value from column 2 (which is column B, the product name). If that product ID exists, great. If it doesn’t, #N/A appears.

Now wrap it with IFERROR: =IFERROR(VLOOKUP(D2, A:B, 2, FALSE), “Not found”)

If the product ID in D2 exists, you get the product name. If it doesn’t exist, you see “Not found” instead of #N/A. Much cleaner.

You can adjust the error message to fit your use case. Use an empty string if you prefer blank cells: =IFERROR(VLOOKUP(…), “”). Use 0 if you want a numeric placeholder. Use “Discontinued” if that’s more meaningful in your context.

Copy this formula down for every row in your data, and suddenly your sheet looks professional instead of broken.

IFERROR with Division

Percentage calculations and ratios are common in spreadsheets, but they frequently encounter the divide-by-zero problem. If you’re calculating percentage growth, margin percentages, or any ratio, and the denominator is sometimes zero, you need IFERROR.

Basic formula: =IFERROR(A2/B2, 0)

If B2 is zero, this returns 0 instead of #DIV/0!. If B2 has a value, the division proceeds normally.

A more descriptive approach: =IFERROR(A2/B2, “N/A”)

This shows “N/A” instead of 0 or an error, which makes it clear that the calculation couldn’t be performed rather than the result actually being zero.

Here’s a real scenario: you’re calculating profit margin for products. The formula is (selling price minus cost) divided by selling price. In a spreadsheet, that might be =(B2-C2)/B2. If the selling price in B2 is zero (perhaps because it’s a free item or a placeholder), you get #DIV/0!. Wrap it: =IFERROR((B2-C2)/B2, “”)

Now free items show a blank cell instead of an error, which is both accurate and clean.

For more complex percentage calculations, you can combine IFERROR with the IF formula in Google Sheets to add conditional logic. For example: =IFERROR(IF(B2=0, “”, A2/B2), “Error”) checks if the denominator is zero before dividing, which prevents the error altogether.

IFERROR vs. IFNA: Choosing the Right Function

Google Sheets offers two error-handling functions that are easy to confuse: IFERROR and IFNA. Understanding the difference helps you write safer, more precise formulas.

IFERROR catches all errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, #ERROR!. It’s a broad net that catches anything that goes wrong.

IFNA catches only #N/A errors. It has the same syntax as IFERROR: =IFNA(value, value_if_na). It returns the error value if and only if the first part produces #N/A. Other errors pass through unchanged.

When should you use each? IFERROR is your go-to for most situations. It’s simpler and handles everything. Use IFNA when you specifically want to handle #N/A errors from VLOOKUP or similar lookup functions, but you want other errors to show up so you can see and fix them.

Example: =IFNA(VLOOKUP(D2, A:B, 2, FALSE), “Not found”) catches the #N/A from a failed lookup but lets other errors through. If your VLOOKUP formula has a syntax error, you’ll see it instead of hiding it with IFERROR.

In practice, IFERROR is safer for production sheets because it prevents any error from breaking your layout. IFNA is better during development and debugging because it lets you spot formula problems.

Nested IFERROR: Trying Multiple Lookups

Sometimes a single lookup isn’t enough. You want to try finding a value in one table, and if that fails, try a second table. This is where nested IFERROR comes in.

Imagine you have a product database in columns A:B and a secondary archive database in columns D:E. You want to look up a product first in the main database, and if it’s not there, look it up in the archive.

=IFERROR(VLOOKUP(G2, A:B, 2, FALSE), IFERROR(VLOOKUP(G2, D:E, 2, FALSE), “Not found”))

This formula tries the first VLOOKUP. If it succeeds, you get the result. If it fails with #N/A, it tries the second VLOOKUP. If that also fails, it returns “Not found.”

You can nest as many IFERROR functions as you need, though too many levels get confusing. Three levels (trying three different lookups) is about the practical limit before readability suffers.

A variation: try looking up a customer in one sheet, and if not found, look them up in a different sheet that has archive customers:

=IFERROR(VLOOKUP(B2, Sheet2!A:D, 3, FALSE), IFERROR(VLOOKUP(B2, Sheet3!A:D, 3, FALSE), “Customer not found”))

This pattern is powerful for multi-source data scenarios where you need to check multiple tables in sequence.

IFERROR with IMPORTRANGE

IMPORTRANGE pulls data from another Google Sheet. It’s a powerful tool for consolidating data from multiple sources, but it can fail if the source sheet is deleted, the connection breaks, or permissions change. IFERROR handles these gracefully.

=IFERROR(IMPORTRANGE(“https://docs.google.com/spreadsheets/d/SHEET_ID/edit”, “Sheet1!A:A”), “Connection failed”)

If the import succeeds, you see the data. If the source sheet is unavailable or the formula has an issue, you see “Connection failed” instead of an error that makes your sheet look broken.

This is especially important in dashboards that pull from multiple sheets. If one source becomes unavailable, you want the dashboard to show a message rather than covering itself in error codes.

Leaving the Error Blank vs. Zero vs. Custom Text

What you show in place of an error matters more than you might think. Different scenarios call for different approaches.

Blank cells (empty string “”) are neutral and clean. Use them when you want to show nothing rather than an error, like in a lookup that might not always find a match. Example: =IFERROR(VLOOKUP(…), “”) leaves the cell empty if the lookup fails.

Zero is appropriate when the calculation actually should be zero if the condition fails. This is common in percentage calculations or ratio sheets where “no data” reasonably means zero. Example: =IFERROR(A2/B2, 0) shows zero if the division cannot be performed.

Custom text like “Not found,” “Pending,” “N/A,” or “Check source” provides context. This helps users understand what went wrong and what to do about it. It’s the most user-friendly option but only works if the context makes sense. You cannot use custom text in formulas that feed into further calculations because “Not found” is text, not a number, and it will break any math based on that cell.

The rule of thumb: blank cells for cosmetic purposes, zero for numeric contexts where zero is meaningful, and custom text for dashboards and reports where users need to understand what happened.

Practical Use Cases

Four real scenarios show IFERROR solving actual problems.

Use case one: Multi-sheet product lookup. You manage inventory across three warehouses in three different sheets. You want a master inventory dashboard that looks up product availability in each sheet. Each lookup might fail if the product doesn’t exist in that warehouse.

Formula: =IFERROR(VLOOKUP(B2, Warehouse1!A:C, 3, FALSE), 0) returns the quantity from warehouse 1, or zero if the product is not there. Copy this down for every product and across for every warehouse, and you have a clean grid showing inventory by location with zeros instead of error codes.

Use case two: Grade calculator. You have a spreadsheet tracking student grades. Each grade contributes to a final score using a formula. But sometimes grades are missing (value is blank) or invalid, causing #VALUE! errors.

Instead of having blanks break your formula, use IFERROR: =IFERROR((A2*0.3 + B2*0.4 + C2*0.3), “Incomplete”)

If any input is invalid or missing, you see “Incomplete” instead of an error. The teacher knows the grade cannot be calculated yet.

Use case three: Inventory status check. You’re looking up whether a product is in stock using data from an external system. If the product doesn’t exist in the database, #N/A appears.

Formula: =IFERROR(VLOOKUP(B2, Products!A:D, 4, FALSE), “Unknown”)

In stock status looks professional instead of broken, and users see “Unknown” for products not in the system.

Use case four: Commission calculator. Salespeople earn different commission percentages based on tier, looked up from a tier table. If someone’s tier is miscoded, the lookup fails.

Formula: =IFERROR(Sales_Amount * VLOOKUP(Tier, TierTable, 2, FALSE), 0)

If the tier lookup fails, commission defaults to zero, which triggers an alert to the manager that something needs fixing. No errors obscure the data.

Common Pitfalls with IFERROR

IFERROR is powerful, but it can hide problems if you’re not careful.

Hiding real errors is the biggest risk. If your formula has a typo or a structural problem, IFERROR masks it by showing your fallback value. You might not realize the formula is broken because everything looks fine. The solution: during development, test without IFERROR first, verify the formula works, then wrap it with IFERROR. During maintenance, occasionally review your error-handling to spot formula problems.

Masking broken formulas leads to silent data corruption. If you use IFERROR to replace errors with zero, and the actual issue is a missing data source, you might think you have zero inventory when you actually have unknown inventory. The context and your error message matter tremendously.

Performance on large ranges: IFERROR adds computational overhead. If you use it on thousands of cells with complex nested formulas, your sheet will recalculate slowly. In most cases this is imperceptible, but in very large sheets, be mindful.

Over-relying on IFERROR instead of fixing the root cause is a common bad habit. If your sheet constantly produces errors that you handle with IFERROR, the underlying data or formula structure probably needs fixing. IFERROR is a temporary band-aid for truly exceptional cases, not a substitute for proper data validation and formula design.

Combining IFERROR with Other Formulas

IFERROR works well alongside other Google Sheets functions to build sophisticated data processing pipelines.

With COUNTIF, COUNTIF in Google Sheets counts cells matching criteria. Wrap it with IFERROR to handle cases where no matches are found: =IFERROR(COUNTIF(A:A, B2), 0) returns zero if the count fails for any reason.

With SUMIF, SUMIF in Google Sheets sums cells matching criteria. Handle errors gracefully: =IFERROR(SUMIF(Category, D2, Amount), 0)

With conditional logic, you can combine IFERROR with the IF formula in Google Sheets to handle multiple scenarios: =IFERROR(IF(A2=0, “”, A2/B2), “Invalid”)

With data validation and remove duplicates in Google Sheets, you can ensure clean data goes into your formulas first, minimizing the errors that IFERROR needs to catch.

For dashboard-building with conditional conditional formatting in Google Sheets, combine IFERROR formulas with color-coding to highlight errors or special values.

Advanced Techniques

Power users sometimes build sophisticated error handling that goes beyond the basics.

Logging errors: instead of hiding errors silently, add a parallel column that notes when an error was caught. Formula: =IF(ISNA(VLOOKUP(…)), “Error: Not found”, VLOOKUP(…)) checks if an error would occur and logs it while still providing a fallback value. This helps with debugging without cluttering the main output.

Chained lookups with smart fallbacks: =IFERROR(VLOOKUP(B2, Primary, 2, 0), IFERROR(VLOOKUP(B2, Secondary, 2, 0), IFERROR(VLOOKUP(B2, Archive, 2, 0), “Not found”))) tries three sources in order, perfect for multi-tiered data systems.

Combining with IMPORTRANGE for resilient dashboards: =IFERROR(SUMIF(IMPORTRANGE(…), …), 0) pulls data from another sheet and returns zero if the connection fails, so your dashboard never breaks.

Final Thoughts

IFERROR is one of those underrated functions that separates sloppy sheets from professional ones. Every spreadsheet has errors waiting to happen, and IFERROR lets you handle them gracefully instead of showing broken formulas to your audience.

Start by identifying the most common errors in your work. Are you doing lots of VLOOKUP? Wrap them with IFERROR and replace #N/A with “Not found.” Doing division calculations? Handle divide-by-zero with =IFERROR(numerator/denominator, 0). Once you get comfortable with the basic pattern, you’ll see opportunities to use it everywhere.

The real power comes when you combine IFERROR with other functions like VLOOKUP, share a Google Sheet with others and let them navigate error-free sheets, and build dashboards that stay professional even when data sources fail temporarily.