IFERROR and IFNA in Excel: catching errors without hiding the real ones

What do IFERROR and IFNA do in Excel?

IFERROR shows something else when a formula returns an error value, and otherwise just shows the result. =IFERROR(E11/D11,"-") divides E11 by D11 and shows a dash if that doesn't work. IFNA does the same, but only for the #N/A error, which you get when a lookup finds nothing. In a Dutch copy of Excel, they're called ALS.FOUT and ALS.NB.

Both are handy, and both can hide exactly what you should have seen. That's why the examples below use two sheets from our Excel course: The Splash swimming pool in Tamworth and Flying Velo bike couriers in Manchester. If you'd rather learn it step by step, follow the lesson on error values.

How do you write IFERROR and IFNA?

Both functions have two required arguments: the formula you want to check, and what should appear if it returns an error (Microsoft). In IFNA, the second argument is what you show for #N/A (Microsoft).

=IFERROR(value,value_if_error)
=IFNA(value,value_if_na)

With Dutch or Belgian regional settings you'd write =ALS.FOUT(E11/D11;"-") and =ALS.NB(…;"-"). If the formula works, the function does nothing and you see the result itself. If you leave the second argument empty, IFERROR returns an empty string (Microsoft).

Which errors does IFERROR catch?

All seven error types Microsoft lists for it (Microsoft). Read them before you make them disappear, because each one tells you what's wrong.

How do you catch #DIV/0!?

Manager Dave at The Splash wants to know what each member pays per visit: the amount in column E divided by the visits in column D. Sara has never been and Laura's cell is empty, so the division returns #DIV/0! for both. An empty cell counts as 0 in a calculation.

This is one of the clean uses of IFERROR. The error isn't a mistake: there's nothing to work out for Sara, and Dave wants a calm sheet. Try it yourself in Sara's row.

Your formula ran in your own browser, on a real calculation engine, and then again on a hidden version of the sheet with other amounts.

To be more precise, test the divisor with the IF function: =IF(D11=0,"-",E11/D11). That only catches division by zero. Microsoft warns that IFERROR suppresses all errors, not only #DIV/0! (Microsoft).

How do you catch #N/A in a VLOOKUP?

With IFNA, around the lookup. Dispatcher Kyle at Flying Velo looks up the customer's area for each ride. The customer list is in L2:M6: the code in column L, the area in column M. Ride R10 is for a new customer, K06, who isn't on the list yet. =VLOOKUP(B11,L2:M6,2,FALSE) returns #N/A there.

That's not Kyle's mistake, but a real answer: this customer doesn't exist yet. Let's say so neatly. If VLOOKUP is new to you, the lesson takes it slowly, and the hints below will walk you through it here.

XLOOKUP has this built in, as its fourth argument: =XLOOKUP(B11,L2:L6,M2:M6,"New customer") does the same without IFNA. It's shorter and just as safe. How the two lookups differ is covered in VLOOKUP vs XLOOKUP.

When does IFERROR hide a real mistake?

Whenever your formula itself is broken. IFERROR doesn't know why there's an error, only that there is one. Say Kyle accidentally asks for column 3 from a list of two columns. With IFERROR he notices nothing; with IFNA he does:

With IFERROR, every ride says "New customer", even for your most loyal customers, and nobody gets a warning. The same goes for a typo: =IFERROR(SUMM(E2:E11),0) shows £0 instead of #NAME?, and the sheet looks perfectly healthy.

Write your formula without IFERROR first and check it works. Only wrap the safety net around it once you know which error you're expecting.

What should you show instead of the error?

That's not a detail, because whatever you put there keeps calculating. Dave wants the average per visit from column G. With a dash for Laura and Sara, AVERAGE skips that text and he gets £7.43. With a 0, he gets £5.95: two members who tell you nothing about the price drag the average down.

So only pick 0 if zero is really the answer. A cell that looks empty with "" holds text, and you can't multiply that. For more on what an average does and doesn't include, read the article on AVERAGE.

Which functions go with these?

There's a small group around an error value, and each member has a job of its own. Here they are with the example that suits them best.

An average with no rows returns #DIV/0! too, as you'll see in AVERAGEIF and AVERAGEIFS. Every function is on the Excel cheat sheet with its Dutch name, and the 12 most useful Excel formulas give you a broader start. If you want to learn to read error values on a real sheet, follow module 3 of the Excel course. It's free.