Wrap a formula with a friendly fallback
Catch any formula error and show a clean message or a blank instead of #N/A, #DIV/0!, or #REF! appearing in front of people.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →IFERROR takes only two arguments — the formula, and what to show if it fails — replacing the older IF(ISERROR(formula), fallback, formula) pattern that typed the formula twice and evaluated it twice.
- →It catches every error type in one wrapper: #N/A, #DIV/0!, #VALUE! and #REF! all fall through to the same fallback, so one wrapper covers a lookup, a division, and a text formula alike.
The fine print
- →It isn't selective: a genuine typo in a range or a broken reference hides behind the same friendly message as a normal missing-value case. Use it on formulas whose failure mode is already known and expected.
- →For a lookup where the only error worth catching is a not-found result, IFNA is narrower — it lets other error types, like a bad range, keep surfacing instead of being swallowed.
- →Works identically in Excel and Google Sheets.
Part by part
- =IFERROR(
- Evaluates the first argument and returns the second if it produced any error. It catches every error type without distinguishing between them.
- {{formula}}
- The real work. IFERROR runs it once and, unusually for a wrapper, does not evaluate it twice — older IF-and-ISERROR patterns did.
- {{fallback}}
- What to show instead. An empty string reads as blank; a message reads as an explanation; a zero is usually a mistake.
Common mistakes
=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "")Hides #N/A, which is the useful error, and equally hides #REF! from a deleted column and #NAME? from a typo. A broken formula now looks like a legitimately empty cell.
=IFNA(VLOOKUP(D2, A:B, 2, FALSE), "")=IFERROR(A2/B2, 0)A division by zero becomes a zero, so the total looks complete and is wrong. Zero is a real value and should never stand in for a missing one.
Wrapping an entire report in IFERRORApplied broadly it suppresses the signal that something upstream broke. Errors are how a spreadsheet reports that its assumptions no longer hold, and silencing them wholesale is how a wrong number ships.
Questions
When should I use IFNA instead?
Whenever the only expected error is a failed lookup. IFNA catches #N/A alone and lets #REF!, #NAME? and #VALUE! through, so a genuinely broken formula still announces itself.
What should the fallback be?
An empty string when the absence is expected and unremarkable, or a short message such as "not found" when the reader needs to know. Avoid zero unless zero is genuinely the right value.
Does it slow the sheet down?
Not meaningfully in modern Excel, which evaluates the inner formula once. The older IF(ISERROR(x), fallback, x) pattern evaluated it twice, which is why it was worth replacing on large sheets.
Can I see which error was caught?
Not from IFERROR itself. Use ERROR.TYPE, or nest IFNA inside IFERROR, when you want to distinguish a missing lookup from a structural break — usually worth doing on any sheet others rely on.