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.

Skip to the 3 exercises ↓

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 value ends in any error: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!.
value_if_na
IFNA: what to return when value ends in #N/A. Any other error passes through and stays visible.
Live exampleA division by zero, caught
E4fx
ABCDE
1CampaignSpendClicksSignupsSignup rate
2Spring Email400125075
3Search Ads18003600162
4Trade Show2500000
5Partner Blog30048012
6Retargeting950200090
7Podcast60015012

How Excel reads it

  1. It calculates the first argument, D4/C4.
  2. If that gives a normal result, IFERROR returns it. The second argument is not used.
  3. 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:

Live exampleA lookup that finds nothing
G2fx
ABCDEFG
1CampaignSpendClicksSignupsFindSpend
2Spring Email400125075BillboardNot in list
3Search Ads18003600162
4Trade Show250000
5Partner Blog30048012
6Retargeting950200090
7Podcast60015012

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.

Live example"Not in list" for a campaign that is in the list
G2fx
ABCDEFG
1CampaignSpendClicksSignupsFindSignups
2Spring Email400125075Search AdsNot in list
3Search Ads18003600162
4Trade Show250000
5Partner Blog30048012
6Retargeting950200090
7Podcast60015012

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.

Exercise 1Cost per signup, without the error

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.

E4fx
ABCDE
1CampaignSpendClicksSignupsCost/signup
2Spring Email400125075
3Search Ads18003600162
4Trade Show250000
5Partner Blog30048012
6Retargeting950200090
7Podcast60015012
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.

Exercise 2A lookup that may find nothing

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.

G2fx
ABCDEFG
1CampaignSpendClicksSignupsFindSpend
2Spring Email400125075Billboard
3Search Ads18003600162
4Trade Show250000
5Partner Blog30048012
6Retargeting950200090
7Podcast60015012
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.

Exercise 3Two different errors, one formula

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.

G2fx
ABCDEFG
1CampaignSpendClicksSignupsFindCost/signup
2Spring Email400125075Trade Show
3Search Ads18003600162
4Trade Show250000
5Partner Blog30048012
6Retargeting950200090
7Podcast60015012
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.

Keep going

There is no lesson on IFERROR alone. It comes up inside the lookup lessons, starting with lesson 3: six VLOOKUP drills on a coffee-shop menu, one of them an item that isn't on the menu. Lesson 3 is part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

VLOOKUP function, with live examplesXLOOKUP function, with live examplesIF function, with live examples#N/A error in Excel: meaning and fixes#DIV/0! error in Excel: meaning and fixes#REF! error in Excel: meaning and fixesExcel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: IFERROR function, Microsoft Support: IFNA function, Microsoft Support: SUM function. Last checked November 2026.