Errors in Excel are unavoidable — but they don't have to be ugly. The IFERROR function in Excel lets you catch any error a formula produces and replace it with a custom value: a friendly message, a zero, a dash, or a completely blank cell. In this guide, you will learn exactly how IFERROR works, see six practical examples covering the most common real-world scenarios, and understand when to use IFNA instead.
IFERROR Syntax
The IFERROR function has just two arguments, making it one of the simplest functions to learn in all of Excel:
=IFERROR(value, value_if_error)
| Argument | Required? | What it means |
|---|---|---|
| value | Required | The formula or cell reference to check for an error. This is where you put your normal formula — VLOOKUP, a division, a calculation, etc. |
| value_if_error | Required | What to return if value produces an error. Can be text, a number, a blank string, another formula, or a cell reference. |
How it works: Excel first evaluates
value. If the result is an error, it returns value_if_error. If the result is not an error, it returns the result as normal — IFERROR gets out of the way completely.
Which Errors Does IFERROR Catch?
IFERROR handles all seven standard Excel error types. Knowing what causes each one helps you decide whether to hide it with IFERROR or fix it at the source:
| Error | Common cause | Typical fix with IFERROR |
|---|---|---|
| #N/A | VLOOKUP / XLOOKUP cannot find the lookup value | "Not found" or "" |
| #DIV/0! | Dividing a number by zero or an empty cell | 0 or "—" |
| #VALUE! | Wrong data type — e.g. text where a number is expected | "Invalid input" |
| #REF! | A cell reference is broken — row or column was deleted | Fix the reference; hiding is not recommended |
| #NAME? | Excel doesn't recognise a function name — often a typo | Fix the typo; hiding masks the real issue |
| #NUM! | An impossible number — e.g. square root of a negative | 0 or "" |
| #NULL! | Incorrect use of space as a range operator | Fix the formula; rarely hidden intentionally |
Important: IFERROR hides ALL error types indiscriminately. If you wrap a formula containing a typo (
#NAME?) or a broken reference (#REF!) with IFERROR, you will never see the real problem. Use IFERROR only when you deliberately expect and want to suppress a specific, predictable error.
Example 1: Hide a Division Error (#DIV/0!)
The most common reason to reach for IFERROR is dividing two numbers where the denominator can sometimes be zero — for example, calculating conversion rates, margins, or averages from a column that may contain blank cells.
Calculate profit margin — handle divide-by-zero gracefully
#
A — Product
B — Revenue
C — Profit
D — Margin
1
Laptop
$12,000
$3,600
30%
2
Monitor
$0
$0
#DIV/0!
3
Keyboard
$4,500
$900
20%
-- Without IFERROR (row 2 shows #DIV/0!):
=C2/B2
-- With IFERROR — show a dash when revenue is zero:
=IFERROR(C2/B2, "—")
-- Or return zero instead of a dash:
=IFERROR(C2/B2, 0)
-- Or return a blank cell:
=IFERROR(C2/B2, "")
Row 2 now shows "—" instead of #DIV/0!. Rows 1 and 3 return their normal percentage results — IFERROR doesn't touch them.
Which fallback to choose? Use
"—" or "" when the blank result is meaningfully different from zero (e.g. no sales happened yet). Use 0 when a zero margin is a legitimate, chartable value.
Example 2: IFERROR with VLOOKUP
This is the single most common use of IFERROR in Excel. VLOOKUP returns #N/A whenever it cannot find a match. Wrapping it with IFERROR replaces that ugly error with a clear, user-friendly message.
Look up a product price — show "Not found" if missing
#
A — Product
D — Search
E — Price
1
Laptop — $1,200
Laptop
$1,200
2
Monitor — $320
Tablet
Not found
3
Keyboard — $85
Monitor
$320
-- Without IFERROR (returns #N/A when product not in list):
=VLOOKUP(D2, A:B, 2, FALSE)
-- With IFERROR — returns "Not found" instead of #N/A:
=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "Not found")
-- Return 0 instead (useful when summing results):
=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), 0)
"Tablet" is not in the product list, so row E2 shows "Not found" instead of #N/A. Laptop and Monitor return their prices normally.
Excel 365 / 2021 users: XLOOKUP has a built-in
if_not_found argument that does this automatically without needing IFERROR: =XLOOKUP(D2, A:A, B:B, "Not found"). It is cleaner and more efficient for new workbooks.
Example 3: IFERROR with INDEX MATCH
INDEX MATCH is more flexible than VLOOKUP — it can look left and handle multiple columns easily. Like VLOOKUP, it returns #N/A when no match is found. IFERROR wraps around it in exactly the same way.
Look up employee department — handle missing names cleanly
-- Without IFERROR:
=INDEX(B2:B100, MATCH(E2, A2:A100, 0))
-- With IFERROR — clean fallback when employee not found:
=IFERROR(
INDEX(B2:B100, MATCH(E2, A2:A100, 0)),
"Employee not found"
)
Any name in column E that does not exist in column A returns "Employee not found". Known employees return their department normally.
Formatting tip: When your IFERROR wraps a multi-line formula like INDEX MATCH, break it across lines in the formula bar (Alt+Enter on Windows, Ctrl+Option+Enter on Mac) to keep it readable.
Example 4: Return a Blank Cell Instead of an Error
Sometimes the cleanest fallback is simply nothing — an empty cell. This is useful for dashboards and reports where a visible message would look cluttered, or when the column will be summed and you want errors to contribute nothing.
Show a blank cell — not an error — when formula fails
#
A — Sales
B — Target
C — % of Target
1
$8,400
$10,000
84%
2
$5,200
(blank)
— empty —
3
$11,100
$10,000
111%
-- Empty string "" makes the cell appear blank:
=IFERROR(A2/B2, "")
-- Works with any formula — VLOOKUP, INDEX MATCH, etc.:
=IFERROR(VLOOKUP(D2, A:B, 2, 0), "")
Row 2 shows an empty cell because Target is blank, causing a #DIV/0!. Rows 1 and 3 return their percentage normally.
Empty string vs truly blank:
"" looks empty but the cell is not technically blank — it still contains a formula. Functions like COUNTA will count it. If you need a truly blank cell, that requires a VBA approach; IFERROR cannot produce one.
Example 5: Nested IFERROR — Try Two Lookup Sources
You can nest one IFERROR inside another to build a fallback chain. This is useful when data lives across two separate tables — try the first table, and if that fails, try the second. If both fail, return a final message.
Search Table 1, then Table 2, then show "Not found"
-- Try Table1 first. If that fails, try Table2.
-- If both fail, return "Not found":
=IFERROR(
VLOOKUP(A2, Table1, 2, 0),
IFERROR(
VLOOKUP(A2, Table2, 2, 0),
"Not found"
)
)
Excel checks Table1 first. On failure it tries Table2. Only if both fail does it return "Not found".
Performance note: Each nested IFERROR forces Excel to evaluate the inner formula twice — once to detect the error, once to get the value. With large tables or many rows, this can slow recalculation noticeably. A CONSOLIDATE approach or a helper column that merges the two tables first is faster.
Example 6: Sum a Column That Contains Errors
When a column has errors scattered through it, a plain SUM propagates the error and returns nothing. Wrapping the individual values with IFERROR inside an array treats each error as zero before summing.
Sum a range that includes #DIV/0! or #N/A cells
#
A — Score
Note
1
85
Normal
2
#DIV/0!
Skipped — treated as 0
3
92
Normal
4
#N/A
Skipped — treated as 0
5
78
Normal
-- Plain SUM returns an error if any cell has an error:
=SUM(A1:A5) → #DIV/0!
-- IFERROR inside SUMPRODUCT — maps errors to 0 before summing:
=SUMPRODUCT(IFERROR(A1:A5, 0))
-- Works as Ctrl+Shift+Enter array formula too (older Excel):
{=SUM(IFERROR(A1:A5, 0))}
Result: 255 (85 + 0 + 92 + 0 + 78). The two error cells are treated as zero and excluded from the sum.
IFERROR vs IFNA — Which Should You Use?
Excel introduced IFNA in 2013 as a more targeted alternative to IFERROR. Choosing the right one depends on how much you want to hide:
IFERROR
- Catches all 7 error types
- Use when any error should be hidden — division errors, lookup errors, name errors, etc.
- Available in Excel 2007+
- Risk: can hide real bugs like #NAME? or #REF!
IFNA
- Catches #N/A only
- Use with VLOOKUP / MATCH when you only expect a "not found" error
- Available in Excel 2013+
- Safer: other real errors like #VALUE! or #REF! remain visible
-- IFNA syntax (identical to IFERROR, different scope):
=IFNA(value, value_if_na)
-- Recommended for VLOOKUP — only hides "not found" errors:
=IFNA(VLOOKUP(D2, A:B, 2, 0), "Not found")
Rule of thumb: Use IFNA with lookup functions (VLOOKUP, MATCH, HLOOKUP). Use IFERROR when you genuinely expect and want to suppress any error type — for example, division calculations on incomplete data.
Quick-Reference: Common IFERROR Patterns
| Goal | Formula pattern |
|---|---|
| Show blank instead of error | =IFERROR(formula, "") |
| Show zero instead of error | =IFERROR(formula, 0) |
| Show custom text | =IFERROR(formula, "Not found") |
| VLOOKUP — hide #N/A | =IFERROR(VLOOKUP(…), "—") |
| INDEX MATCH — hide #N/A | =IFERROR(INDEX(MATCH(…)), "") |
| Try table 1, then table 2 | =IFERROR(VLOOKUP(…T1), IFERROR(VLOOKUP(…T2), "—")) |
| Sum a range with errors | =SUMPRODUCT(IFERROR(A1:A100, 0)) |
| #N/A only (lookup) — IFNA | =IFNA(VLOOKUP(…), "Not found") |
Frequently Asked Questions
-
What does IFERROR do in Excel?IFERROR checks a formula for errors. If an error is found, it returns a value you specify — such as text, zero, or a blank cell. If no error is found, it returns the formula's normal result unchanged. It is the simplest and most common error-handling function in Excel.
-
What errors does IFERROR catch?IFERROR catches all seven standard Excel error types: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. It does not natively catch #SPILL! errors introduced with dynamic arrays in newer Excel versions.
-
What is the difference between IFERROR and IFNA?IFERROR catches all error types, while IFNA catches only #N/A errors. Use IFNA with VLOOKUP when you only want to handle "not found" results — that way, other real errors like #VALUE! or #REF! remain visible and alert you to genuine problems in your data.
-
How do I return a blank cell instead of an error in Excel?Use an empty string as the second argument:
=IFERROR(your_formula, ""). The cell will appear blank when an error occurs. Note that the cell still contains a formula — it is not truly empty — so COUNTA will count it. -
Can IFERROR slow down my Excel file?IFERROR itself is lightweight. However, nesting heavy functions like VLOOKUP inside IFERROR means Excel evaluates the inner formula twice — once to detect the error and once to return the result. For large datasets this can slow recalculation. Consider XLOOKUP (built-in error handling) or IFNA (narrower scope) as alternatives.
-
Should I use IFERROR with VLOOKUP?Yes — IFERROR with VLOOKUP is one of the most common Excel formula combinations. It prevents the #N/A error when a lookup value is not found. That said, if you are using Excel 365 or Excel 2021, XLOOKUP with its built-in
if_not_foundargument is a cleaner, faster, and more readable alternative.