IFERROR and IFNA: Handling Excel Errors Properly

IFERROR catches any error a formula produces and lets you replace it with something more useful, like a blank cell or a friendly message. IFNA does the same thing, but only for the specific #N/A error, leaving every other error type untouched. Most people should reach for IFERROR by default, but IFNA has a real place when you specifically want other errors to still show up rather than get hidden.

I have a slightly embarrassing story about this one. Early on, I got into the habit of wrapping absolutely everything in IFERROR and returning a blank result whenever anything went wrong, because it made my spreadsheets look tidy and error-free. I put together a monthly report like this for a while, blank cells instead of red error triangles everywhere, and felt quite pleased with how clean it looked. It wasn't until my manager asked why three clients seemed to have "no data" for two months running that I realised IFERROR had been quietly swallowing a genuine problem — a broken lookup range — the entire time, and I'd mistaken silence for success. The formula wasn't wrong exactly, it was just very good at hiding the fact that something else was.

How does IFERROR actually work?

The structure is straightforward:

=IFERROR(value, value_if_error)

Wrapping a VLOOKUP so it shows "Not found" instead of #N/A:

=IFERROR(VLOOKUP(A2, ClientRange, 3, FALSE), "Not found")

If the VLOOKUP works normally, you get the normal result. If it throws any error at all — #N/A, #REF!, #DIV/0!, whatever — you get "Not found" instead. IFERROR doesn't distinguish between error types; it treats them all the same way.

What's the difference between IFERROR and IFNA?

IFNA only catches the #N/A error specifically, and lets every other error type pass through untouched.

=IFNA(value, value_if_na)

The same VLOOKUP example with IFNA instead:

=IFNA(VLOOKUP(A2, ClientRange, 3, FALSE), "Not found")

If the client ID genuinely isn't in the list, you'll get "Not found," same as before. But if the formula has an actual mistake in it — say, someone accidentally deleted the ClientRange and it now throws #REF! — IFNA lets that error show through as a visible red warning, rather than quietly relabelling it "Not found" the way IFERROR would.

	IFERROR	IFNA
Catches #N/A	Yes	Yes
Catches #REF!, #DIV/0!, #VALUE!, etc.	Yes	No, lets them show
Good for	General cleanup of expected "not found" cases	Lookups specifically, where you want genuine mistakes to stay visible
Risk	Can hide real formula problems	Slightly more setup, but safer for catching mistakes

Why does this distinction actually matter?

This is really the point of the whole post, and it's the lesson from my own mistake above. IFERROR is convenient, but convenient in a way that can work against you. If a formula breaks because of a genuine structural problem — someone deleted a column your formula depended on, a range reference got corrupted, a typo crept into a cell reference — IFERROR will happily catch that too and show your friendly fallback message, giving zero indication anything is actually wrong.

IFNA is more deliberate. It only steps in for the specific, expected case of "this value genuinely isn't in the list," which is usually the only scenario you actually want to handle gracefully. Anything else stays visible as an error, which is exactly what you want when something's broken and needs fixing rather than hiding.

My rule of thumb now: for VLOOKUP, XLOOKUP, or INDEX/MATCH style lookups where "not found" is a normal, expected outcome, I use IFNA. For general formulas where I genuinely want to catch any kind of error and don't need to know which one, IFERROR is fine.

What are the most common Excel error types, and what do they actually mean?

Worth knowing what you're dealing with before deciding whether to catch or fix it.

• #N/A — a lookup couldn't find a match. Often expected and fine to catch.
• #REF! — a formula is referencing a cell or range that no longer exists, usually because something was deleted. This is a genuine problem, not something to quietly hide.
• #DIV/0! — a formula is dividing by zero or by an empty cell. Sometimes expected (like a percentage calculation before any data exists yet).
• #VALUE! — a formula is trying to do maths on something that isn't actually a number, like text mixed into a range.
• #NAME? — usually a typo in a function name, or a name Excel doesn't recognise, like a deleted named range.

Should I use IFERROR everywhere as a habit?

No, and this is worth saying plainly given my own experience with it. Using IFERROR as a blanket habit on every formula, "just in case," tends to hide the exact information you'd need to actually fix a spreadsheet when something goes wrong. It's better used deliberately, on formulas where you've thought through what kind of error is actually expected and what a sensible fallback looks like, rather than pasted onto everything by default.

A decent middle ground: build and test your formulas without any error handling first, confirm they behave correctly, and only wrap them in IFERROR or IFNA once you're confident about what kind of error is genuinely expected and acceptable to hide.

FAQ

Can I use IFERROR with a blank result instead of text? Yes — use empty quotes as the second argument, like =IFERROR(VLOOKUP(...), ""), which returns a blank-looking cell rather than any text.

Does IFERROR slow down my spreadsheet? Not meaningfully for normal use. IFERROR does mean the formula inside it may effectively run twice in older Excel versions (once to check for an error, once to return the result), but for everyday spreadsheet sizes this isn't something you'll notice.

Can I nest IFERROR around an entire IFS or nested IF formula? Yes, though if you're doing this often it's worth checking whether the underlying logic "IF, IFS and nested IF" simplifying rather than just wrapping errors after the fact.

What's the difference between an error and a blank cell in Excel? An error means the formula itself failed to calculate. A blank cell simply has no formula or value at all. IFERROR converts an error into whatever you choose, but that result still exists as actual formula output, not a truly empty cell.

Is there a way to see all the errors in a spreadsheet at once? Yes — use Find & Select (Ctrl+G, then Special) and choose "Formulas" with only the "Errors" checkbox ticked. This selects every cell currently showing an error, which is far quicker than scrolling through manually looking for red triangles.

Should I wrap every VLOOKUP or XLOOKUP in IFERROR by default?
Not automatically — it depends on whether "not found" is a genuinely expected outcome for that particular lookup. If you're still deciding between the two lookup functions themselves, my post on VLOOKUP vs XLOOKUP covers that, and XLOOKUP actually has a built-in "if not found" argument that can remove the need for wrapping it in IFERROR at all.