Advertisement

What You’ll Learn
Error handling in Excel is probably not the first thing you think about when you sit down to build a spreadsheet. You are focused on the formula, the data, the layout. And then you press Enter and half your column fills up with #N/A errors, and suddenly that is the only thing you are thinking about.
Here’s the thing — most people’s response to this situation is to Google “how to remove #N/A in Excel,” find IFERROR, wrap their formula in it, and move on. The errors disappear. The spreadsheet looks clean. Problem solved, right? Not quite. Error handling in Excel done badly is arguably worse than no error handling at all, because it creates the illusion that everything is working when it might not be.
This lesson is going to show you how to do it properly — how to use IFERROR and IFNA correctly, how to choose between them, and how to avoid the trap that catches most intermediate Excel users.
Before you start handling errors, you need to know what Excel is actually trying to tell you. Each error code is a specific message. Ignoring what the message says and just making it disappear is like unplugging a fire alarm because the noise is annoying.
This one is everywhere. #N/A means Excel went looking for something and could not find it. You see it constantly with VLOOKUP, HLOOKUP, MATCH, and XLOOKUP. If you ask Excel to find a value that does not exist in your lookup range, it returns #N/A. The formula is not broken. The data just is not there. That distinction matters a lot for how you respond to it.
#VALUE! shows up when a formula gets fed the wrong kind of data. Try to add a number to a cell that contains the word “none” — typed in manually by a colleague — and you will get #VALUE!. The formula cannot process text where it expects a number.
#REF! means a cell reference in your formula points to a location that no longer exists. Delete a column that a formula depends on, and everything referencing that column turns into #REF! immediately. Almost always caused by someone reorganising the spreadsheet without checking what formulas were using it.
#DIV/0! appears whenever a formula divides by zero or an empty cell. You see this constantly in financial models where denominators — revenue, units, headcount — start out blank before data is entered.
| Error Code | What It Means | Common Cause |
|---|---|---|
| #N/A | Value not available | Lookup value missing from table |
| #VALUE! | Wrong data type | Text in a numeric field |
| #REF! | Broken cell reference | Row or column was deleted |
| #DIV/0! | Division by zero | Denominator is zero or empty |
| #NAME? | Unrecognised function name | Typo in formula name |
| #NULL! | Invalid intersection | Space used instead of comma/colon |
| #NUM! | Invalid numeric value | Square root of a negative number |
The Excel IFERROR function is the most commonly used error handler in Excel. The idea is simple: you give it a formula to run, and you tell it what to return if that formula produces an error. Any error. All seven of them.
Syntax
IFERROR(value, value_if_error)
Example — VLOOKUP with IFERROR
=IFERROR(VLOOKUP(A2, RepTable, 2, 0), "New Rep")
Example — Chained IFERROR (two lookup tables)
=IFERROR(VLOOKUP(A2, CurrentDB, 2, 0), IFERROR(VLOOKUP(A2, ArchiveDB, 2, 0), "Not found"))
You manage a sales report. You use VLOOKUP to pull each rep’s region from a reference table. Three new reps joined this month and have not been added to the table yet. Every row for those reps shows #N/A, and your report looks like it is falling apart before Monday morning’s meeting.
Wrapping in IFERROR returns “New Rep” for any rep not found in the table. The report is clean, the label is informative, and you can still see which rows need updating. If you want a solid foundation in VLOOKUP before combining it with error handlers, Lesson 12 on VLOOKUP at XplorExcel walks through it in full detail.
Advertisement
When you write IFERROR, you have to choose what goes in the second argument. Most people choose whatever looks cleanest in the cell and move on. That is a mistake. Your options:
"" — the cell looks blank"Not found" or "Check data"⚠️ Common Mistake
Returning 0 from IFERROR in a column that gets averaged. If 20 of your 100 rows have lookup errors and IFERROR fills them with 0, your AVERAGE formula now divides the total by 100 instead of 80 — making the average artificially low. Use "" or a text label for genuinely missing data. Reserve 0 only for situations where zero is actually the correct value.
Here is where most tutorials drop the ball. They teach you IFERROR and send you on your way. But there is a second function — IFNA — and for lookup formulas specifically, it is almost always the better choice.
IFNA works exactly like IFERROR with one key difference: it only catches the #N/A error. Everything else — #VALUE!, #REF!, #DIV/0!, #NAME? — passes through unchanged.
Syntax
IFNA(value, value_if_na)
Example — IFNA with VLOOKUP
=IFNA(VLOOKUP(A2, RepTable, 2, 0), "New Rep")
Trust me on this — at first glance IFNA looks like a downgrade. Why would you want a function that catches fewer errors? The answer is that formula bugs are not the same as missing data, and you should not treat them the same way.
When VLOOKUP returns #N/A because a value is not in your table, that is an expected outcome. You want to handle it gracefully. But if VLOOKUP returns #REF! because someone deleted a column, that is a genuine bug. You need to see that error so you can fix it. IFNA gives you clean output for the expected case while letting actual formula errors stay visible.
| Function | Errors Caught | Best For | Risk Level |
|---|---|---|---|
| IFERROR | All 7 error types | Tested formulas, #DIV/0! in calculators, chained fallbacks | Higher — hides bugs |
| IFNA | #N/A only | VLOOKUP, HLOOKUP, MATCH, INDEX-MATCH where missing data is normal | Lower — bugs stay visible |
💡 Pro Tip
Default to IFNA for any lookup formula. Most tutorials teach IFERROR as the go-to function — which is why so many inherited spreadsheets quietly produce wrong answers. Every formula bug gets hidden behind a clean-looking fallback. Make IFNA your first instinct for lookups and you will catch problems earlier and debug workbooks much faster.
This is the section most tutorials skip entirely, and it is probably the most important part of this lesson.
Error handling in Excel becomes a liability when you use it to make a broken spreadsheet look fixed without actually fixing anything. It is the spreadsheet equivalent of putting a rug over a crack in the floor. The floor is still cracked.
Here is how this plays out. You have 500 rows of VLOOKUP results. Fifty of them return #N/A because those records genuinely do not exist in the reference table. You wrap the whole column in IFERROR with "" as the fallback. The errors disappear. Two weeks later, someone asks for an AVERAGE of that column. Excel calculates the average over 450 rows with no indication that 50 rows of data are absent.
You’ve probably seen a financial model or tracker where the totals seem off but nobody can explain why. The column sums to a plausible number. No errors are visible. But the number is wrong. Nine times out of ten, the culprit is an IFERROR applied too broadly on a formula that had a bug nobody ever saw.
The principle: understand the error before you decide how to handle it. Error handling should be a deliberate choice made after you know what is causing the error — not a first instinct applied to make red cells disappear.
Once you are comfortable with the basics, IFERROR becomes more than just a cleanup tool. You can use it to build actual conditional logic into your formulas.
Imagine you are reconciling invoices against two separate databases — a current system and an older archive. You want to check the current system first. If the invoice is not there, check the archive. If it is in neither place, return “Not found.”
Chained IFERROR — Two-Table Lookup
=IFERROR(VLOOKUP(A2, CurrentDB, 2, 0), IFERROR(VLOOKUP(A2, ArchiveDB, 2, 0), "Not found"))
If you have moved to XLOOKUP, you may not need IFERROR at all. XLOOKUP has a built-in fourth argument called if_not_found that handles the #N/A case directly:
XLOOKUP with built-in error handling
=XLOOKUP(A2, LookupRange, ReturnRange, "Not found")
Adding IFERROR around an XLOOKUP that already has an if_not_found argument is redundant. Lesson 18 on XLOOKUP at XplorExcel covers its full argument set — worth reading before deciding how to structure your lookup formulas going forward.
If you ever open a spreadsheet built more than ten or fifteen years ago, you will likely see something like this:
Legacy Pattern (pre-Excel 2007)
=IF(ISERROR(VLOOKUP(A2, RepTable, 2, 0)), "Not found", VLOOKUP(A2, RepTable, 2, 0))
This runs the VLOOKUP twice — once to check for an error, once to return the result. It works, but it is verbose and slower to calculate. IFERROR replaced this pattern in Excel 2007. You do not need to write it yourself, but you need to recognise it when you open an inherited file.
When you hit errors in a spreadsheet — especially one you did not build — resist the urge to immediately wrap everything in IFERROR. Follow this sequence first.
Debugging Workflow — Step by Step
Remove the error handler temporarily
Strip out the IFERROR or IFNA wrapper. Change IFERROR(VLOOKUP(...), "") back to just VLOOKUP(...). You need to see the raw error.
Identify the error type
Read the error code. #N/A means the lookup value is missing. #REF! means a reference is broken. #VALUE! means data types are mismatched. Each one tells you where to look next.
Use Evaluate Formula
Go to the Formulas tab and click Evaluate Formula. Excel steps through the formula one piece at a time and shows you exactly where it breaks. Essential for nested formulas.
Fix or wrap deliberately
If it is a formula bug — wrong reference, deleted column, typo — fix the root cause. If the error is expected and acceptable, wrap it with IFNA or IFERROR with a meaningful fallback value. Never skip steps 1 to 3.
💡 Pro Tip
While building a complex model, add a temporary “Error Check” column. Use IFERROR(yourFormula,"ERROR") to flag failures as “ERROR” and successes as “OK.” Once everything shows “OK,” delete the column. It is a simple habit that saves hours of debugging later.
🧪 Try It Yourself
Set up a small workbook with two sheets. On Sheet1, put five product codes in column A and five prices in column B. On Sheet2, put only three of those five product codes in column A, and their descriptions in column B.
if_not_found argument — IFERROR is often redundant when using it.For the authoritative technical reference on syntax, argument descriptions, and version compatibility:
← Previous Lesson
Next Lesson →
Work through the questions in Excel, then check your answers.
Advertisement