IFERROR in Excel: Replace Errors With Something Useful
Use IFERROR to replace #N/A, #DIV/0! and other errors with a blank, zero or message. Examples with lookups and division, and when hiding errors is a bad idea.
By Ayoub Farid · · 1 min read
IFERROR returns your formula's result if it works, and something else if it produces an error. It's the polite way to show "Not found" instead of #N/A, or a blank instead of #DIV/0!.
Syntax
=IFERROR(value, value_if_error)With lookups
=IFERROR(VLOOKUP(F2,$A$2:$C$100,3,FALSE),"Not found")(XLOOKUP has this built in: =XLOOKUP(F2,A:A,C:C,"Not found").)
With division
Margin per row, where some rows have no sales yet:
=IFERROR((B2-C2)/B2,"")See #DIV/0! error.
IFNA: only catch #N/A
=IFNA(VLOOKUP(F2,$A$2:$C$100,3,FALSE),"Not found")IFNA only replaces #N/A. Other errors (like a broken reference) still show. For lookups, that's often safer.
When not to use IFERROR
IFERROR hides every error, including real mistakes: a typo in a function name (#NAME?), a deleted column (#REF!), text where numbers should be (#VALUE!). Wrapping everything in IFERROR can turn a broken report into a report that looks fine but is wrong.
Good practice:
- Use it only where an error is expected (not found, nothing to divide by).
- Prefer IFNA for lookups.
- Return something visible like "Check" rather than 0, so problems stand out.
Errors and totals
A single error in a range breaks SUM. If you must total a column that contains errors:
=AGGREGATE(9,6,B2:B100)(9 = SUM, 6 = ignore errors.)
Frequently asked questions
How do I hide #N/A in Excel?
Wrap the formula: =IFERROR(formula,"") or =IFNA(formula,"").
What is the difference between IFERROR and IFNA?
IFERROR catches all errors; IFNA only catches #N/A.
Can IFERROR return 0?
Yes: =IFERROR(formula,0). Be careful, since 0 can look like a real result.
Does IFERROR slow down Excel?
Not noticeably in normal sheets.