How do I make IFERROR return a blank instead of zero?

Use an empty string as the second argument: =IFERROR(VLOOKUP(A2,D:F,3,0),""). The cell looks blank, but the result is text — so if a =SUM reads that cell it ignores it rather than adding a number. Use 0 instead when a total downstream must treat the fallback as a real value.

Should I nest IFERROR or use IFS?

They solve different problems. IFERROR reacts to whether a formula errored; IFS chooses an output based on conditions you write. Nesting two IFERROR calls is the right move for a fallback chain — try one lookup, then a second, then a default — while IFS is for branching on values, not on errors.

Can IFERROR catch a #SPILL! error?

Yes, IFERROR catches #SPILL! like any other error, but you usually shouldn't. A #SPILL! means a dynamic array can't expand because cells in its path are occupied. Hiding it with IFERROR suppresses the entire array result — clear the blocking cells instead so the formula spills correctly.

Does IFERROR slow down a large workbook?

On big sheets it can, because IFERROR must first evaluate the wrapped formula, and a heavy VLOOKUP runs in full before the fallback ever applies. For tens of thousands of lookups, switch to XLOOKUP with its built-in if_not_found argument, which returns the fallback in a single function call instead of wrapping a second function around the lookup.

How to Use IFERROR in Excel

Last updated June 2026

Quick answer

IFERROR wraps a formula and returns a fallback you choose whenever that formula errors: =IFERROR(VLOOKUP(A2,D:F,3,0),"Not found") shows Not found instead of #N/A. The syntax is =IFERROR(value, value_if_error). It catches every error type — #N/A, #DIV/0!, #REF!, #VALUE!, #NAME?, and #SPILL! — so reach for IFNA when you only want to hide a failed lookup.

What is the syntax of IFERROR?

IFERROR takes two arguments. The first is the formula you want to run; the second is what to show if that formula returns any error. If the first argument works, you get its normal result and Excel never looks at the second.

=IFERROR(value, value_if_error)

value is the formula to evaluate. value_if_error is returned only when value produces #N/A, #DIV/0!, #REF!, #VALUE!, #NAME?, #NULL!, #NUM!, or #SPILL!.

How do you wrap a formula in IFERROR?

Start with the formula that errors
Say B2 holds a lookup that returns #N/A when the ID in A2 isn't in your table. Click the cell and look at the formula bar — you'll wrap exactly what's already there.
=VLOOKUP(A2,D:F,3,0)
Type IFERROR around it
Move your cursor to just after the =, type IFERROR(, then go to the end of the formula and add a comma plus your fallback in quotes. The original formula becomes the first argument untouched.
=IFERROR(VLOOKUP(A2,D:F,3,0),"Not found")
Decide between text, blank, or zero
Use "Not found" for a human reading the sheet, "" to leave the cell visually empty, or 0 if a downstream =SUM needs a number. A blank string is text, so it won't add cleanly into a total — that distinction matters.
=IFERROR(A2/B2,0)
Copy it down the column
Grab the fill handle at the bottom-right of the cell and drag down, or double-click it to fill to the end of the adjacent data. The relative references shift per row exactly as they would without IFERROR.

Should I use IFERROR or IFNA?

This is the decision that separates a clean sheet from a sheet that silently lies to you. IFERROR catches every error. IFNA catches only #N/A. With lookups, IFNA is usually the right call: a missing match is expected and you want to hide it, but a #REF! from a deleted column or a #NAME? from a typo is a real bug you need to see, not bury under "Not found".

=IFNA(VLOOKUP(A2,D:F,3,0),"Not found")

Hides a missing match but still lets a broken reference surface as #REF!. Use IFNA for lookups; reserve IFERROR for cases where you genuinely want to swallow any error, like a division that may hit a blank divisor.

Error	What it actually means	Catch or fix?
`#N/A`	A lookup found no match for the value you searched for.	Catch with `IFNA` — a missing match is usually expected.
`#DIV/0!`	A formula divided by zero or by an empty cell.	Depends — catch a genuinely empty divisor, but investigate if the zero is broken data.
`#REF!`	A reference points at a cell or sheet that was deleted.	Fix — this is a structural break; hiding it loses real results.
`#VALUE!`	A formula got the wrong type, like text where it needs a number.	Fix — clean the input; an IFERROR here masks bad data.
`#NAME?`	Excel doesn't recognize a function or named range — usually a typo.	Fix — correct the spelling; this is never a value to hide.
`#SPILL!`	A dynamic-array result can't spill because cells in its path are occupied.	Fix — clear the blocking cells; `IFERROR` would hide the whole array.

The errors IFERROR will swallow — and which ones you should fix at the source instead of hiding.

Don't blanket-wrap every formula in IFERROR just to make a sheet look tidy. Hiding #DIV/0! or #VALUE! can mask broken inputs that produce wrong numbers, not just ugly ones. Wrap the one formula that has a known, expected failure mode — and leave the rest visible so real mistakes still shout.

A real example: the debt payoff tracker

Our debt payoff tracker uses this pattern in the Months to Payoff column. NPER returns #NUM! when a debt's minimum payment is smaller than its monthly interest — the balance never shrinks, so there's no finite number of months. The formula catches that one error and shows a plain-English warning instead of a cryptic code.

=IF(B6="","",IFERROR(CEILING(NPER(C6/12,-(D6+IF(E6=1,$B$3,0)),B6),1),"check inputs"))

The outer IF skips empty rows. The IFERROR turns a #NUM! from an impossible payoff into check inputs, which tells the user their payment can't cover interest — far more useful than #NUM!.

Frequently asked questions

How do I make IFERROR return a blank instead of zero?: Use an empty string as the second argument: =IFERROR(VLOOKUP(A2,D:F,3,0),""). The cell looks blank, but the result is text — so if a =SUM reads that cell it ignores it rather than adding a number. Use 0 instead when a total downstream must treat the fallback as a real value.
Should I nest IFERROR or use IFS?: They solve different problems. IFERROR reacts to whether a formula errored; IFS chooses an output based on conditions you write. Nesting two IFERROR calls is the right move for a fallback chain — try one lookup, then a second, then a default — while IFS is for branching on values, not on errors.
Can IFERROR catch a #SPILL! error?: Yes, IFERROR catches #SPILL! like any other error, but you usually shouldn't. A #SPILL! means a dynamic array can't expand because cells in its path are occupied. Hiding it with IFERROR suppresses the entire array result — clear the blocking cells instead so the formula spills correctly.
Does IFERROR slow down a large workbook?: On big sheets it can, because IFERROR must first evaluate the wrapped formula, and a heavy VLOOKUP runs in full before the fallback ever applies. For tens of thousands of lookups, switch to XLOOKUP with its built-in if_not_found argument, which returns the fallback in a single function call instead of wrapping a second function around the lookup.