IFERROR vs IFNA in Excel: Replace #N/A and #DIV/0! the Right Way
Rani looks up product code P109 in her price list. It is not there, so VLOOKUP shows #N/A. In another cell she divides sales by orders for a new city with 0 orders and gets #DIV/0!. Her report looks broken, although nothing is really wrong. IFNA and IFERROR replace those errors with a clean message.
IFERROR(formula, value) returns the value if the formula gives any error. IFNA(formula, value) returns it only for #N/A, the "not found" error from lookups. Other errors still show, so real mistakes stay visible.
Download the practice file (iferror-ifna.xlsx)
Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- For lookups, wrap with IFNA:
=IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found"). - For divisions, wrap with IFERROR:
=IFERROR(B2/C2,0). - With XLOOKUP, use its own fourth argument:
=XLOOKUP(E2,A2:A6,C2:C6,"Not found"). - Do not wrap whole complicated formulas in IFERROR while you are still building them.
=IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found")
=IFERROR(B2/C2,0)
The price list
| Code | Product | Price |
|---|---|---|
| P101 | Milk 1L | 56 |
| P102 | Bread | 40 |
| P103 | Paneer 200g | 90 |
| P104 | Curd 400g | 35 |
| P105 | Butter 100g | 58 |
Example results
| Formula | Result |
|---|---|
=VLOOKUP("P103",A2:C6,3,FALSE) | 90 |
=VLOOKUP("P109",A2:C6,3,FALSE) | #N/A |
=IFNA(VLOOKUP("P109",A2:C6,3,FALSE),"Not found") | Not found |
=XLOOKUP("P109",A2:A6,C2:C6,"Not found") | Not found |
=1200/0 | #DIV/0! |
=IFERROR(1200/0,0) | 0 |
=IFNA(VLOOKUP("P103",A2:C6,4,FALSE),"Not found") | #REF! (column 4 does not exist, so the mistake stays visible) |
Why IFNA is safer for lookups
With IFERROR(VLOOKUP(...),"Not found"), a typing mistake in the column number (#REF!) or a misspelt function (#NAME?) also shows "Not found". You think the product is missing, but the formula is broken. IFNA hides only the honest "not found" case.
Which one when
| Situation | Use |
|---|---|
| Lookup that may not find a value | IFNA, or XLOOKUP's if_not_found |
| Division where the bottom can be 0 or blank | IFERROR(…,0) or IF(C2=0,0,B2/C2) |
| Whole report, final polish | IFERROR only after you have checked the formulas |
| Formula you are still building | Nothing; let the errors show |
A cleaner division without IFERROR: =IF(C2=0,0,B2/C2). It only handles the zero case and keeps other errors visible.
Ravindra Bagale's Tip
Build the formula first, check that it is right, and add IFERROR or IFNA last. If you wrap it on day one, you will never see the mistakes you need to fix.
Ravindra Bagale's Tip – मराठी
आधी formula बनवा, बरोबर आहे का ते तपासा, आणि IFERROR किंवा IFNA शेवटी लावा. पहिल्याच दिवशी wrap केलं तर सुधारायच्या चुका कधी दिसणारच नाहीत.
Ravindra Bagale's Tip – हिंदी
पहले formula बनाइए, सही है या नहीं जाँचिए, और IFERROR या IFNA आख़िर में लगाइए। पहले ही दिन wrap किया तो सुधारने वाली गलतियाँ कभी दिखेंगी ही नहीं।
Practice task
- Open the Lookup sheet. F2 has P109, which is not in the list.
- Check G2 shows Not found (IFNA).
- Change F2 to P104. Expect 35.
- Check G5 shows 0 instead of #DIV/0!.
- Change the column number in G2 to 4 and see #REF! appear; IFNA does not hide it.
Learn it properly
Course lessons:
Related guides: Excel VLOOKUP Exact Match · XLOOKUP for Beginners · XLOOKUP with Multiple Criteria (Product and City) · IF Function
Frequently asked questions
What is the difference between IFERROR and IFNA?
IFERROR catches every error type; IFNA catches only #N/A.
Does IFNA work in Excel 2010?
No. IFNA arrived in Excel 2013. Use IFERROR in older versions.
How do I show a blank instead of an error?
Use two double quotes: =IFNA(VLOOKUP(...),"").
Should I use IFERROR with XLOOKUP?
Usually no. XLOOKUP's fourth argument handles not-found values by itself.