The guide · IFERROR
Syntax
=IFERROR(value, value_if_error)
=IFNA(value, value_if_na)Excel works out the first argument. If it's an error, you get the second argument instead. If not, you get the result as normal.
IFERROR or IFNA?
IFERROR catches every error: #N/A, #REF!, #DIV/0!, #VALUE!, #NAME?. IFNA only catches #N/A, which is what a lookup gives when a value isn't found. For lookups, IFNA is the safer choice: "not found" becomes your message, and a real mistake in the formula still shows up.
The trap: hiding a bug
Suppose a lookup asks for column 3 of a 2-column table. Every row shows #REF!. Wrap it in IFERROR and every row says "Not listed". The sheet looks tidy and is completely wrong. So before you wrap a formula:
- Read the error. #N/A means "not found". #REF!, #VALUE! and #NAME? usually mean the formula itself is wrong.
- Fix the formula until only the rows you expect show an error.
- Then wrap it, and check that the message appears only where it should.
What the errors mean
| Error | Usually means |
|---|---|
#N/A | a lookup didn't find the value |
#REF! | a reference to a cell or column that doesn't exist |
#DIV/0! | dividing by zero or an empty cell |
#VALUE! | the wrong kind of value, such as text in arithmetic |
#NAME? | a misspelled function, or text without quotes |
Common mistakes
- Wrapping a broken formula. If every row shows your message, the formula is the problem, not the data.
- Returning 0.
IFERROR(…, 0)looks like a real price and goes into totals unnoticed. A word or an empty string is easier to spot. - Using IFERROR where IFNA would do. IFNA lets real mistakes stay visible.
FAQ
- How do I use IFERROR with VLOOKUP?
- Wrap the whole lookup: =IFERROR(VLOOKUP(A2,$D$2:$E$6,2,FALSE),"Not listed"). If the lookup returns an error, the cell shows Not listed instead.
- What is the difference between IFERROR and IFNA?
- IFERROR replaces any error. IFNA replaces only #N/A, the not-found error from lookups, so other mistakes in the formula stay visible.
- How do I hide #DIV/0! in Excel?
- Use IFERROR, for example =IFERROR(B2/C2,""), or test the divisor first: =IF(C2=0,"",B2/C2).
- Is it bad to use IFERROR?
- Not when the error is expected, like a product that isn't in a price list. It is bad when it hides a formula mistake, because the sheet looks fine and is wrong.