Excel practice › Excel functions › IFERROR and IFNA
IFERROR and IFNA in Excel: catch an error without hiding a mistake
IFERROR returns the result of a formula, or a value you choose when that formula ends in an error. IFNA does the same for one error only, #N/A. To show 0 instead of #DIV/0! when the clicks in C4 are zero: =IFERROR(D4/C4, 0).
Use IFNA around lookups, where #N/A means "not found" and is the only error you expect. IFERROR catches every error, including the ones that are telling you the formula itself is broken.
Syntax
=IFERROR(value, value_if_error) · =IFNA(value, value_if_na)
value- The formula to try, like a division or a lookup. If it works, its result is returned unchanged.
value_if_error- IFERROR: what to return when
valueends in any error:#N/A,#VALUE!,#REF!,#DIV/0!,#NUM!,#NAME?or#NULL!. value_if_na- IFNA: what to return when
valueends in#N/A. Any other error passes through and stays visible.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Spend | Clicks | Signups | Signup rate |
| 2 | Spring Email | 400 | 1250 | 75 | |
| 3 | Search Ads | 1800 | 3600 | 162 | |
| 4 | Trade Show | 2500 | 0 | 0 | 0 |
| 5 | Partner Blog | 300 | 480 | 12 | |
| 6 | Retargeting | 950 | 2000 | 90 | |
| 7 | Podcast | 600 | 150 | 12 |
D4/C4 is a division by zero and IFERROR returns the 0 instead. Delete the wrapper and leave =D4/C4 to see the error it was covering.How Excel reads it
- It calculates the first argument,
D4/C4. - If that gives a normal result, IFERROR returns it. The second argument is not used.
- If it gives an error, IFERROR returns the second argument instead.
The replacement can be a number, text in quotes, or two quotes with nothing between them ("") for a cell that looks empty. Choose with the next formula in mind: in a column you will total, a 0 is counted, and text is skipped by SUM but breaks a formula that adds the cells with +.
IFNA for lookups
A lookup that finds nothing returns #N/A. That is an expected outcome, not a broken formula, and IFNA replaces exactly that one error. There is no campaign called Billboard in this list:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Campaign | Spend | Clicks | Signups | Find | Spend | |
| 2 | Spring Email | 400 | 1250 | 75 | Billboard | Not in list | |
| 3 | Search Ads | 1800 | 3600 | 162 | |||
| 4 | Trade Show | 2500 | 0 | 0 | |||
| 5 | Partner Blog | 300 | 480 | 12 | |||
| 6 | Retargeting | 950 | 2000 | 90 | |||
| 7 | Podcast | 600 | 150 | 12 |
Podcast in place of F2, in quotes, and the lookup's own result (600) comes through.The mistake: IFERROR hides a broken formula
IFERROR can't tell "not found" from "this formula is wrong". Search Ads is in the list, and this formula still says it isn't. The real problem is the table range: A2:C7 is three columns wide and the formula asks for column 4, which is a #REF! error. IFERROR covers it with the same text it uses for a missing name.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Campaign | Spend | Clicks | Signups | Find | Signups | |
| 2 | Spring Email | 400 | 1250 | 75 | Search Ads | Not in list | |
| 3 | Search Ads | 1800 | 3600 | 162 | |||
| 4 | Trade Show | 2500 | 0 | 0 | |||
| 5 | Partner Blog | 300 | 480 | 12 | |||
| 6 | Retargeting | 950 | 2000 | 90 | |||
| 7 | Podcast | 600 | 150 | 12 |
IFERROR to IFNA and the cell shows #REF!, which points at the real problem. Then widen the table to A2:D7 and the answer is 162.The same thing happens with a misspelled function name, a deleted column or text where a number should be: IFERROR returns your replacement and the mistake stays in the workbook. So the rule is: IFNA around lookups, IFERROR only where you know which error to expect and why, like a division by a number that can be zero.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
Cost per signup is spend divided by signups. The trade show has no signups, so the division fails. In E4, calculate the trade show's cost per signup, and return the text No signups when the division gives an error.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Campaign | Spend | Clicks | Signups | Cost/signup |
| 2 | Spring Email | 400 | 1250 | 75 | |
| 3 | Search Ads | 1800 | 3600 | 162 | |
| 4 | Trade Show | 2500 | 0 | 0 | |
| 5 | Partner Blog | 300 | 480 | 12 | |
| 6 | Retargeting | 950 | 2000 | 90 | |
| 7 | Podcast | 600 | 150 | 12 |
Show the answer
=IFERROR(B4/D4,"No signups") gives No signups.
B4/D4 divides by zero. IFERROR returns the text instead of #DIV/0!, and returns the division's own result as soon as D4 holds a number above 0.
F2 holds a campaign name. In G2, return that campaign's spend, or the text Not in list when the name isn't in column A. Catch only the not-found error.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Campaign | Spend | Clicks | Signups | Find | Spend | |
| 2 | Spring Email | 400 | 1250 | 75 | Billboard | ||
| 3 | Search Ads | 1800 | 3600 | 162 | |||
| 4 | Trade Show | 2500 | 0 | 0 | |||
| 5 | Partner Blog | 300 | 480 | 12 | |||
| 6 | Retargeting | 950 | 2000 | 90 | |||
| 7 | Podcast | 600 | 150 | 12 |
Show the answer
=IFNA(VLOOKUP(F2,A2:D7,2,FALSE),"Not in list") gives Not in list.
IFNA returns the lookup's result when there is one and your text when the lookup ends in #N/A. Any other error would still show, which is what you want from a lookup.
F2 holds a campaign name. In G2, return its cost per signup: its spend divided by its signups. Return the text No data when the campaign isn't in the list or has no signups.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Campaign | Spend | Clicks | Signups | Find | Cost/signup | |
| 2 | Spring Email | 400 | 1250 | 75 | Trade Show | ||
| 3 | Search Ads | 1800 | 3600 | 162 | |||
| 4 | Trade Show | 2500 | 0 | 0 | |||
| 5 | Partner Blog | 300 | 480 | 12 | |||
| 6 | Retargeting | 950 | 2000 | 90 | |||
| 7 | Podcast | 600 | 150 | 12 |
Show the answer
=IFERROR(VLOOKUP(F2,A2:D7,2,FALSE)/VLOOKUP(F2,A2:D7,4,FALSE),"No data") gives No data.
A name that isn't in the list gives #N/A, and a campaign with 0 signups gives #DIV/0!. Both are expected here, so IFERROR around the whole division is the right choice. For Podcast the same formula returns 600 ÷ 12 = 50.
Questions people ask
What is the difference between IFERROR and IFNA?
IFERROR replaces every error: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!. IFNA replaces only #N/A, the "not found" error of lookups, and lets the others show. IFNA is the safer one around a lookup, because a mistake in the formula stays visible.
How do I use IFERROR with VLOOKUP?
Put the whole VLOOKUP in the first argument and the replacement in the second: =IFERROR(VLOOKUP(F2,A2:D7,2,FALSE),"Not found"). IFNA takes the same two arguments and is the better fit here. See VLOOKUP for the lookup itself.
How do I make IFERROR return blank instead of 0?
Use two double quotes with nothing between them as the second argument: =IFERROR(D4/C4,""). The cell then shows nothing when the formula fails.
Should I wrap every formula in IFERROR?
No. An error is information: it says a value is missing, a range is wrong or a name is misspelled. Wrap a formula only when you know which error it can produce and what it means, and prefer IFNA when that error is #N/A.