IFERROR returns a value you choose when a formula produces an error. It keeps reports clean.
Syntax
=IFERROR(value, value_if_error)
With VLOOKUP
=IFERROR(VLOOKUP(F2, A2:C100, 3, FALSE), "Not found")
If the lookup fails, the cell shows "Not found" instead of #N/A.
With division
=IFERROR(A2/B2, 0) returns 0 instead of #DIV/0! when B2 is zero or blank.
Show an empty cell
=IFERROR(A2/B2, "") leaves the cell looking blank.
Which errors does it catch?
All of them: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!.
When not to use it
Because IFERROR hides every error, it can hide real mistakes such as a misspelled function name. Use IFNA when you only expect #N/A from a lookup. And test your formula without IFERROR first.
Worked example
Column A has sales and column B has units: A2 = 500, B2 = 0. =A2/B2 shows #DIV/0!. Use =IFERROR(A2/B2,0) and the cell shows 0. For a price list, =IFERROR(VLOOKUP(F2,A2:C100,3,FALSE),"Not found") shows "Not found" when the code in F2 is missing.
Common mistakes
- Wrapping a whole long formula in IFERROR before testing it. A typo then shows as your fallback value and you never notice.
- Using 0 as the fallback in a sum or average. A fake 0 pulls an average down. Use "" or NA() when the value is truly missing.
- Using IFERROR when you only expect #N/A. IFNA is safer because it lets real errors show.
Frequently asked questions
What is the difference between IFERROR and IFNA?
IFERROR catches every error type. IFNA catches only #N/A, so other errors such as #REF! still show and you can fix them.
Can I show a blank instead of a value?
Yes. Use =IFERROR(your_formula,""). The cell looks empty, but it holds an empty text, so some formulas may treat it as text.
Does IFERROR slow down Excel?
Not noticeably on normal files. On very large lookups it runs your formula once, so the cost is small.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: IFERROR, IFNA, VLOOKUP.
Start Excel practice → Case studies
