formulastreak

IFERROR and IFNA

Updated · Works in: Excel 2013+, Microsoft 365, Google Sheets · Every example checked before release

Short answer

=IFERROR(value, fallback) returns the fallback when the value is any error, e.g. =IFERROR(C2/B2,0). Prefer IFNA around lookups (it only catches #N/A), and testing the cause with IF when you know it.

IFERROR replaces an error with something friendlier. It's useful, and easy to overuse: an error is often telling you something real. Here's how to use it well.

The problem

Two campaigns haven't started, so their clicks are 0 and the conversion divides by zero. The #DIV/0! errors also break anything that uses these cells, like an average.

Marketing campaigns · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = 3.0%
ABCDE
1CampaignClicksSalesConversionOwner
2Spring email1200363.0%Mara
3Instagram860121.4%
4Display ads00#DIV/0!Jonas
5Search ads2300693.0%Priya
6Newsletter540275.0%
7Partner blog00#DIV/0!Kenji

IFERROR(value, fallback)

IFERROR returns the value when it's fine and the fallback when it's any error. Here the fallback is 0.

Marketing campaigns · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = 3.0%
ABCDE
1CampaignClicksSalesConversionOwner
2Spring email1200363.0%Mara
3Instagram860121.4%
4Display ads000.0%Jonas
5Search ads2300693.0%Priya
6Newsletter540275.0%
7Partner blog000.0%Kenji

Often better: test the cause

IFERROR also hides typos and broken references. When you know what causes the error, test for it directly. This version shows an empty cell only when Clicks is 0; any other problem still shows up.

Marketing campaigns · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = 3.0%
ABCDE
1CampaignClicksSalesConversionOwner
2Spring email1200363.0%Mara
3Instagram860121.4%
4Display ads00Jonas
5Search ads2300693.0%Priya
6Newsletter540275.0%
7Partner blog00Kenji

Blank cells: ISBLANK

ISBLANK(E2) is TRUE only for a truly empty cell, so IF(ISBLANK(E2), "Needs owner", E2) fills the gaps. E2="" also catches formulas that return empty text.

Marketing campaigns · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = Mara
ABCDEF
1CampaignClicksSalesConversionOwnerOwner check
2Spring email120036MaraMara
3Instagram86012Needs owner
4Display ads00JonasJonas
5Search ads230069PriyaPriya
6Newsletter54027Needs owner
7Partner blog00KenjiKenji

IFNA for lookups

IFNA(value, fallback) catches only #N/A, the “not found” error of lookups, and lets every other error through. That's usually what you want around a VLOOKUP.

Orders and price list · edit D2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultD2 = Monstera
ABCDEFGHIJ
1OrderSKUQtyProductSKUProductPriceStock
22001PL-1042MonsteraPL-101Fern$9.5040
32002PT-2101Terracotta potPL-104Monstera$34.0012
42003TL-3013Watering canPL-107Snake plant$22.0025
52004PL-1011FernPT-210Terracotta pot$12.5060
62005SL-4014Potting mixPT-212Ceramic pot$19.0018
72006PL-1041MonsteraTL-301Watering can$18.009
82007PT-2122Ceramic potSL-401Potting mix$7.2580
92008PL-9991Unknown SKU

Check yourself

One real exercise from the “Decisions” unit. Checked on our server; no account needed.

Work out each campaign's conversion (Sales in C ÷ Clicks in B) in D2, and leave the cell empty ("") when Clicks is 0. Test the clicks with IF instead of catching the error.

  • Use IF
  • Without IFERROR
Select D2 and type = to start its formula (in the cell or the formula bar)
ABCDE
1CampaignClicksSalesConversionOwner
2Spring email120036Mara
3Instagram86012
4Display ads00Jonas
5Search ads230069Priya
6Newsletter54027
7Partner blog00Kenji

Common mistakes

  • Wrapping every formula in IFERROR. A typo or a deleted column then shows a calm 0 instead of a warning.

  • Leaving out the second argument: IFERROR(A1/B1) isn't valid.

  • Using IFERROR around a lookup when IFNA would do: IFERROR also hides a wrong column number (#REF!).

Questions

What's the difference between IFERROR and IFNA?+

IFERROR catches every error type. IFNA catches only #N/A, so other mistakes stay visible. IFNA needs Excel 2013 or later; Google Sheets has both.

How do I show a blank instead of an error?+

Use an empty string as the fallback: `=IFERROR(A1/B1,"")`.

Reading is step one. Now make it stick.

Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.

Practise it now