When you build complex dashboards in Google Sheets, you will inevitably encounter errors. A VLOOKUP looking for a non-existent employee ID will return #N/A. A formula attempting to divide by zero will return #DIV/0!. A typo in a function name will return #NAME?.
While these error codes are helpful for debugging, they make your final spreadsheet look broken and unprofessional. Furthermore, if a downstream formula relies on a cell containing an error, the error will cascade, breaking your entire financial model.
To prevent this, you should wrap volatile formulas in the IFERROR function. In this guide, you will learn how to use IFERROR to gracefully hide mistakes and output custom text instead.
The IFERROR Syntax
The IFERROR function acts as a safety net. It requires two arguments:
=IFERROR(value, [value_if_error])- value: This is the formula (like a VLOOKUP) that you want Google Sheets to attempt to run.
- value_if_error: This is the fallback. If the first formula fails, what should Google Sheets display instead?
Use Case 1: Hiding Errors with Blank Cells
The most common use of IFERROR is to make a broken cell appear completely blank, keeping your dashboard visually clean.
Suppose you have a VLOOKUP searching for an invoice number: =VLOOKUP(A2, Data!A:C, 3, FALSE).
If A2 is currently empty, the VLOOKUP will panic and display #N/A. To fix this, wrap the entire formula in IFERROR, and use double quotation marks ("") as the fallback.
=IFERROR(VLOOKUP(A2, Data!A:C, 3, FALSE), "")Now, if the VLOOKUP fails, Google Sheets will output a perfectly blank cell instead of an ugly error code.
Use Case 2: Providing Custom Warning Messages
Instead of hiding the error entirely, you might want to alert the user that they made a mistake without breaking the spreadsheet.
Suppose you are building a budget calculator that divides total cost (A2) by the number of participants (B2): =A2/B2.
If a user enters 0 for participants, they will get a #DIV/0! error. You can replace this with a helpful instruction:
=IFERROR(A2/B2, "Please enter at least 1 participant")If they enter a valid number, the math calculates normally. If they enter 0, the cell gently prompts them to fix their input.
Use Case 3: Chaining Fallback Formulas
The value_if_error argument does not have to be text; it can be a completely different formula. This is incredibly powerful for fallback searches.
Suppose you are looking up a product price. You want to check the “2024 Pricing” sheet first. If the product isn’t there (resulting in an error), you want the formula to automatically search the “2023 Pricing” sheet instead.
=IFERROR(VLOOKUP(A2, '2024 Pricing'!A:B, 2, FALSE), VLOOKUP(A2, '2023 Pricing'!A:B, 2, FALSE))This creates a resilient, multi-tiered search. By wrapping your core logic in IFERROR, you ensure that unexpected data never brings your workflow to a grinding halt.