IFERROR

How IFERROR works

IFERROR runs your formula. If it returns a normal value, IFERROR passes that value through untouched. If the formula returns any Excel error, IFERROR swaps in your fallback instead.

It catches every error type at once: #N/A from a missing lookup, #DIV/0! from dividing by zero, #VALUE! from a type mismatch, #REF! from a deleted column, #NAME? from a typo, #NUM!, #NULL!, even the new #CALC!. One wrapper, all errors handled.

That broad reach is also IFERROR's biggest risk. A typo that should surface as #NAME? gets silently replaced by your fallback string and the bug ships to production. When you only want to handle a missing lookup, reach for IFNA instead so real bugs still bubble up.

= IFERROR(value, value_if_error)

Arguments

IFERROR around a VLOOKUP: kill the #N/A

VLOOKUP returns #N/A when the lookup value isn't in the range. That ugly red error makes a clean report look broken. IFERROR wraps the lookup so missing IDs return a friendly label instead.

Below: a price lookup against a product catalog. Two of the IDs in column D aren't in the catalog. The bare VLOOKUP throws #N/A. The IFERROR-wrapped version returns "Not found" for the same rows.

=IFERROR(VLOOKUP(D2,A2:B11,2,FALSE),"Not found")

• •VLOOKUP(D2,A2:B11,2,FALSE) -> the risky formula - returns the price or #N/A
• •"Not found" -> the fallback - shown only when VLOOKUP errors
• •Result -> matched IDs return the price; missing IDs return the friendly text
• •Same row count -> IFERROR doesn't change the spill or shape, just swaps errors for fallbacks
• •Pair with FALSE -> exact match VLOOKUP plus IFERROR is the safest production lookup pattern

Hide division by zero with a 0 fallback

Revenue per unit is revenue divided by units sold. Some products sold zero units this month. Bare division returns #DIV/0! and the column lights up red. Wrap with IFERROR and pass 0 - missing data reads cleanly as zero.

=IFERROR(B2/C2,0)

• •B2/C2 -> the division - errors when C2 is zero
• •0 -> the fallback - same numeric type as the rest of the column so SUM still works
• •Numeric fallback -> use 0 (not "" or text) when downstream cells will sum or average the column
• •Watch the meaning -> 0 here means "no sales to compute against", not "zero revenue per unit" - your dashboard label needs to be honest

Blank-on-error with "" for clean reports

Sometimes you want missing values to read as blank, not as text and not as zero. Pass "" (an empty string) as the fallback. Stakeholders see an empty cell instead of #N/A or 0, which is the right answer for headcount lists, optional fields, and any report where missing means missing.

=IFERROR(VLOOKUP(A2,$D$2:$E$8,2,FALSE),"")

• •"" -> empty string fallback - cell renders blank
• •Fixed range -> $D$2:$E$8 with $ so the lookup range stays put when dragging down
• •Versus 0 -> "" reads as blank in the report; 0 reads as a real value and pollutes averages
• •Versus a label -> "" beats "Not found" when downstream tools or pivot tables expect either a value or nothing
• •Trade-off -> blank cells can hide data quality issues - if a missing match should trigger a follow-up, use a visible label

Nested IFERROR: try primary, then fallback list

Some lookups have two sources. Try the primary catalog first; if the SKU isn't there, check the secondary supplier list. Nested IFERROR runs the second lookup only when the first fails - a clean two-tier fallback in one cell.

The pattern extends as deep as you need: IFERROR(A, IFERROR(B, IFERROR(C, "None"))). Each layer is a fresh attempt; the final fallback is the safety net.

=IFERROR(VLOOKUP(A2,$D$2:$E$6,2,FALSE),IFERROR(VLOOKUP(A2,$G$2:$H$6,2,FALSE),"Not stocked"))

• •First VLOOKUP -> checks primary catalog D:E - returns the price if found
• •Second VLOOKUP -> runs only when the first errors - checks supplier list G:H
• •"Not stocked" -> the final catch-all when neither source has the SKU
• •Order = priority -> primary first, fallback second - whatever wins is whatever ships
• •No double evaluation -> if the primary hits, Excel never runs the supplier lookup - faster on big sheets

IFERROR vs IFNA: when each one is right

Excel can throw eight different error types: #N/A, #REF!, #VALUE!, #DIV/0!, #NAME?, #NUM!, #NULL!, and #CALC!. Each one means something specific and useful. IFERROR catches all eight. IFNA catches only #N/A. The difference looks small until your dashboard breaks silently in front of a stakeholder.

Think about what each error actually tells you when it shows up in a VLOOKUP. #N/A means "the lookup value is not in the lookup range" - that is the function working correctly, just reporting that nothing matched. Every other error means something else broke: a column was deleted (#REF!), a number got typed as text (#VALUE!), the function name was misspelled (#NAME?), the result is too large (#NUM!). Those are formula bugs, not data outcomes.

When you wrap a VLOOKUP in IFERROR with the fallback "Not found", you tell Excel "replace any error - any of the eight - with the words Not found." That works perfectly when the only error you ever expect is #N/A. The day a teammate deletes a referenced column and your formula starts returning #REF!, IFERROR quietly converts every #REF! to "Not found" too. The dashboard now reads "Not found" everywhere. Looks fine. Numbers are wrong. Nobody notices for a week.

IFNA is the surgical version. It catches only the #N/A - the missing-match case the lookup was designed to produce - and lets every other error display as a red error in the cell. Genuine bugs surface immediately. Missing matches still get a clean fallback. You get the friendly UX without the silent corruption.

=IFNA(VLOOKUP(A2,$E$2:$F$5,2,FALSE),"Not found")

• •IFNA(value, value_if_na) -> catches ONLY #N/A - the "lookup value not in range" error - and replaces it with the fallback. Available since Excel 2013.
• •IFERROR(value, value_if_error) -> catches ALL eight error types and replaces each with the same fallback. Available since Excel 2007.
• •The trade-off -> IFERROR is broader so it accidentally hides bugs. IFNA is precise so bugs stay visible as red errors you can investigate.
• •Rule for production lookups -> use IFNA. The legitimate "no match" case gets handled cleanly, and any future formula breakage shows up as a real error instead of silent bad data.
• •When IFERROR is correct -> rare. Use it when you genuinely want to swallow every error - for example, a calculation column where any failure should display 0 so a SUM keeps working. Document why.
• •XLOOKUP alternative -> the modern XLOOKUP has if_not_found built into its arguments. No wrapper needed at all; same precise behavior as IFNA.

Where people go wrong

1. 01
   IFERROR hides the bug

   Wrapping a VLOOKUP that's silently returning #N/A because of a typo in the lookup column means you'll never see the typo. The report looks fine, the data is wrong, and the issue ships unnoticed.

   Fix: Use IFNA when you only want #N/A handled. It catches the missing-match case without swallowing #REF!, #VALUE!, or #NAME? - so genuine bugs still surface as visible red errors.
2. 02
   Used inside SUM as a "default zero"

   =SUM(IFERROR(A1:A10/B1:B10, 0)) used to require Ctrl+Shift+Enter as an array formula in Excel 2019 and earlier, otherwise it only evaluates the first row. People copy the formula, see one number, and think it works.

   Fix: On Excel 365 dynamic arrays just work - the formula evaluates row by row automatically. On older Excel, enter with Ctrl+Shift+Enter or rebuild with SUMPRODUCT(IFERROR(A1:A10/B1:B10,0)).
3. 03
   Wrong fallback type

   Returning "N/A" or "Not found" when the next formula expects a number breaks downstream math: =SUM(D:D) chokes on the text and returns #VALUE!, or worse, silently treats the strings as zero and gives a wrong total.

   Fix: Match the fallback to what the column needs. If the column is summed, return 0. If the column is text, return the label. If the column feeds another VLOOKUP, return whatever that lookup expects.
4. 04
   Wrapping the entire formula instead of the risky part

   =IFERROR(VLOOKUP(A2,table,2,FALSE) * B2 / C2, 0) catches the lookup error, the multiply error, and the divide error all at once. If C2 is zero you get 0 back and have no idea which step actually failed.

   Fix: Wrap only the part that can legitimately error. =IFERROR(VLOOKUP(...),0) * B2 / C2 fails loudly on the divide-by-zero so you can debug it, while still handling the missing-lookup case.

Notes

• Available since Excel 2007 - works in every modern version including Excel for Mac and Excel Online.
• Catches every Excel error type: #N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NUM!, #NULL!, and the newer #CALC!.
• IFNA is the narrower cousin - it only catches #N/A and is usually the right choice for production VLOOKUP/INDEX-MATCH wrappers.
• Nest IFERROR inside another IFERROR for a fallback chain - try source A, then source B, then a final default string.
• Don't blanket-wrap formulas you haven't tested - debugging a sheet where every cell silently returns 0 is brutal.
• Pair with VLOOKUP, INDEX/MATCH, or HLOOKUP for the classic production lookup pattern - IFERROR catches the missing-match #N/A.
• XLOOKUP has a built-in if_not_found argument that replaces IFERROR for lookups - if you're on Excel 365, prefer XLOOKUP and skip the wrapper.

What to learn next

Now prove it