IFERROR in Excel: Syntax, Examples and Errors
Updated 2026-10-13 · Free · Works in Excel & Google Sheets
Syntax
=IFERROR(value, value_if_error)Quick answer
IFERROR wraps a formula and returns a fallback when it errors: =IFERROR(A2/B2, 0) shows 0 instead of #DIV/0!. It catches every error, including #REF! and #NAME? — great for polishing results, risky on structural breaks, because a broken reference starts to look like clean data.
Syntax
=IFERROR(value, value_if_error)| Argument | What it means |
|---|---|
| value | The formula or expression to evaluate: a division like A2/B2, a lookup like VLOOKUP(...), or any formula that might return an error. |
| value_if_error | What to return when value produces any error — 0, "" for an empty-looking cell, a text message in quotes, or another formula. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =IFERROR(A2/B2; 0). This is standard in countries where the comma is the decimal separator.
Basic examples
=IFERROR(A2/B2, 0)The classic divide-by-zero guard: when B2 is empty or 0, the division would return #DIV/0!, and IFERROR swaps it for 0. Ratios and percentages in a dashboard stay clean instead of spraying error codes down the column.
=IFERROR(VLOOKUP(E2, $A$2:$C$50, 3, FALSE), "Not found")A friendlier lookup: when E2 has no match in the table, VLOOKUP returns #N/A and the fallback shows "Not found" instead. For lookup misses specifically, IFNA does the same job while still exposing other errors.
=IFERROR(VALUE(A2), A2)Converts text like "123" into the number 123, and when A2 holds text that cannot convert (or already has letters), it returns the original content instead of #VALUE!. A one-cell rescue for mixed columns.
=IFERROR(A2/B2, "")Shows an empty-looking cell instead of an error. Mind the difference: "" prints nothing but is still a text value, so AVERAGE and charts treat the cell as text, not as a blank they can skip.
IFERROR catches every error, not just the one you expect: =IFERROR(A2/B2, 0) also turns the #REF! of a deleted column into a tidy 0. Wrap the narrowest expression possible, and fix structural errors instead of dressing them up.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| #NAME? | IFERROR is misspelled — or your Excel is set to another language, where the function has a localized name (SI.ERROR, SIERREUR, WENNFEHLER). | Type =IF and let autocomplete complete the name, or open the file in the matching language version of Excel. |
| The error still shows | The failing part sits outside the wrapper, or the fallback itself errors: =IFERROR(A2/B2, C2/D2) shows #DIV/0! again when D2 is 0, because the fallback is what breaks. | Wrap the fallback too — =IFERROR(A2/B2, IFERROR(C2/D2, 0)) — or fix the source of the error inside it. |
| Real errors disappear | IFERROR swallowed a structural error: the #REF! of a deleted column now returns 0, and nobody notices the table is broken. | Keep the wrapper narrow and fix the cause. For lookup misses use IFNA, which leaves #REF! and other errors visible. |
| Zeros and blanks flow into other formulas | The fallback 0 joins AVERAGE as a real value, and "" breaks arithmetic downstream with #VALUE!. | Choose the fallback to match the destination: 0 for sums, "" only where text is safe, or NA() to keep genuine gaps out of averages. |
| A column half numbers, half text | In =IFERROR(VALUE(A2), A2), the fallback returns the original text, so the column ends up mixing numbers and text. | Clean the source first — remove stray spaces and convert text-numbers — then a plain VALUE works with no fallback needed. |
Invisible spaces cause lookup misses too: VLOOKUP reports #N/A for "Paid " with a trailing space, IFERROR hides it as "Not found", and the real cause stays buried. Clean the column first, and the fallback rarely fires.
IFERROR vs. IF + ISERROR
| IFERROR | IF + ISERROR | |
|---|---|---|
| Arguments | Two — the expression and the fallback | Three — the test, the fallback, and the expression written a second time |
| Available in | Excel 2007 and later (and Google Sheets) | Every Excel version back to 2003 (and Google Sheets) |
| Calculation cost | The expression is evaluated once | The expression is evaluated twice — once inside ISERROR, again as the result — noticeable on large ranges |
=IF(ISERROR(A2/B2), 0, A2/B2)The old-school equivalent of =IFERROR(A2/B2, 0). It exists because IFERROR did not exist before Excel 2007 — files shared with very old versions still need this form. In any modern Excel or Google Sheets, IFERROR is shorter, evaluates once and is the default choice.
IFERROR in Google Sheets
Google Sheets supports IFERROR with the same two arguments and behavior as Excel, so the formulas above can be pasted over unchanged.
- It catches the same set of errors: #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!.
- In regional settings that use a comma as the decimal separator (much of Europe and Latin America), type semicolons between arguments: =IFERROR(A2/B2; 0). Excel behaves the same way.
- Sheets also has IFNA, which catches only #N/A — the better guard for VLOOKUP and other lookups when you still want structural errors to show.
- For error-proofing whole results, Sheets also offers IFERROR as an array formula: one wrapper can cover an entire output range at once.
Limitations and pro tips
- IFERROR hides the error type: #N/A, #REF! and #DIV/0! all collapse into the same fallback. Diagnose with the bare formula first, then wrap it once it is correct.
- Use IFNA instead when only lookup misses matter — it leaves #REF! and friends visible, so a broken range cannot masquerade as "Not found".
- The fallback is a value, not a blank: "" looks empty but is text, and 0 shifts averages. Pick the fallback that fits what happens next.
- Do not wrap whole columns by reflex: thousands of IFERROR cells slow audits and bury data problems. Fix the source, keep the guard narrow.
- For a fallback that preserves the signal, return NA() instead of 0: =IFERROR(VLOOKUP(...), NA()) keeps genuine misses visible in charts and statistics.
IFERROR is polish, not repair. Stabilize the formulas first, then use it to keep final reports clean — and fill the leftover gaps so the polished area reads like finished data.
Related Tools
Related Functions
Frequently Asked Questions
What does IFERROR do in Excel?
It evaluates a formula and returns a fallback when the result is any error: =IFERROR(A2/B2, 0) shows 0 instead of #DIV/0!. If the formula succeeds, its normal result passes through unchanged — IFERROR never alters correct values.
Which errors does IFERROR catch?
All of them: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. That breadth is both the strength and the risk — the innocent #N/A you meant to catch and structural errors get hidden together.
How do I use IFERROR with VLOOKUP?
Wrap the whole lookup: =IFERROR(VLOOKUP(E2, $A$2:$C$50, 3, FALSE), "Not found"). When E2 has no match, the cell shows "Not found" instead of #N/A. Prefer IFNA for this case — it catches only #N/A and still reveals other errors.
What is the difference between IFERROR and IFNA?
IFERROR catches every error, while IFNA catches only #N/A. For lookups, IFNA is usually the right choice: a miss becomes "Not found", but a broken reference (#REF!) still shows so you can fix it.
Why is my IFERROR formula still showing an error?
Either the failing part sits outside the wrapper, or the fallback itself errors: =IFERROR(A2/B2, C2/D2) shows #DIV/0! when D2 is 0. Wrap the fallback too, or fix the error inside it.
Can IFERROR return an empty cell?
It can return an empty-looking cell: =IFERROR(A2/B2, "") prints nothing. But "" is a text value, not a true blank — AVERAGE, COUNT and charts treat it as text. Use 0 or NA() when downstream formulas need to tell the gap apart.
Does IFERROR work in Google Sheets?
Yes — same name, same two arguments, same behavior. Only the argument separator changes in comma-decimal regional settings, where you type semicolons: =IFERROR(A2/B2; 0). Sheets also offers IFNA with the same meaning as in Excel.
Ready to try it on your own file?
Open Fill Blank Cells in Excel — It's Free