Logical
How to Use IFERROR in Excel (Hide #N/A and #DIV/0!)
IFERROR catches any error a formula produces and returns a value of your choice instead (a friendly "Not found", a 0, or a blank cell), so dashboards stay clean.
It is most often wrapped around lookups like VLOOKUP and XLOOKUP and around division. This guide shows the pattern and when to use the narrower IFNA instead.
IFERROR syntax
=IFERROR(value, value_if_error) | Argument | Required | What it does |
|---|---|---|
value | Required | The formula or expression to evaluate, e.g. a VLOOKUP or a division. |
value_if_error | Required | What to return if value produces any error: text in "quotes", a number, or "" for a blank cell. |
Note: Catches every error type (#N/A, #DIV/0!, #VALUE!, and more). To trap only #N/A, use IFNA. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Friendly message for a failed VLOOKUP
=IFERROR(VLOOKUP(E2, A2:C100, 3, FALSE), "Not found") If VLOOKUP returns #N/A, IFERROR swaps in "Not found". This is the most common use of IFERROR.
2. Avoid a divide-by-zero error
=IFERROR(B2/C2, 0) A division by zero returns #DIV/0!; IFERROR replaces it with 0 so totals and charts keep working.
3. Return a blank instead of an error
=IFERROR(XLOOKUP(E2, A:A, C:C), "") Two double quotes with nothing between them return an empty string, so the cell simply looks blank on a miss.
How to write IFERROR step by step
- 1
Build and test your formula first so you know it works.
- 2
Click into the cell and wrap the formula: type =IFERROR( before it.
- 3
After the formula, type a comma.
- 4
Enter what to show on error ("Not found", 0, or "" for blank), then close the bracket ) and press Enter.
- 5
Fill the formula down the column as needed.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Hides real mistakes | IFERROR masks every error, including genuine formula bugs you would want to see. | Add IFERROR only after the formula works, and use IFNA when you only want to catch #N/A. |
Returns 0 unexpectedly | value_if_error was left empty, which Excel treats as 0. | Use "" (two quotes) if you want a blank cell instead of 0. |
Too few arguments | The comma between value and value_if_error is missing. | Make sure there are exactly two arguments separated by a comma. |
#NAME? | A very old Excel version (2003 or earlier) does not have IFERROR. | Use the longer ISERROR pattern, or update Excel. |
Frequently asked questions
What is the difference between IFERROR and IFNA?
IFERROR catches every error type; IFNA catches only #N/A. IFNA is safer for lookups because it still lets real errors like #VALUE! surface so you can fix them.
Can I use IFERROR with VLOOKUP?
Yes. It is the classic combination: =IFERROR(VLOOKUP(...), "Not found") replaces the #N/A you get when a lookup value is missing.
Does IFERROR slow down my spreadsheet?
It evaluates the inner formula and only computes the fallback on error, so the overhead is tiny. On very large ranges any difference is still usually negligible.
Does IFERROR work in Google Sheets?
Yes. IFERROR and IFNA both work the same way in Google Sheets.