IFERROR explained, with practice exercises

In short: =IFERROR(value,value_if_error). If the formula in the first argument returns an error, Excel shows the fallback. Otherwise you see the normal result.

Try it now

The employee ID in E2 is not in the list, so a plain VLOOKUP returns #N/A. Wrap it in IFERROR so F2 shows "not found" instead. Click the yellow cell and write the formula.

Task The employee ID in E2 does not exist, so a VLOOKUP returns #N/A. Make F2 show “not found” in that case and the department when the ID exists.

	A	B	C	D	E	F
1	Employee ID	Name	Department		Employee ID	Department
2	1012	Murat Kaya	Sales		1050
3	1007	Jana Weber	Purchasing
4	1023	Lena Hoffmann	Finance
5	1044	Sara Klein	Sales
6	1031	Tom Schulz	HR
7	1019	Ali Demir	Purchasing

The IFERROR formula

Argument	Meaning	In the example
value	The formula whose errors you want to catch	VLOOKUP(E2,A2:C7,3,FALSE)
value_if_error	What to show instead: text in quotes, a number, or "" for a blank-looking cell	"not found"

Put together: =IFERROR(VLOOKUP(E2,A2:C7,3,FALSE),"not found"). Excel evaluates the VLOOKUP first. If that produces an error, IFERROR returns the text. If the ID exists, the department passes through untouched.

Which errors does IFERROR catch?

Every error value a formula can return. These are the ones you will meet most:

Error	German Excel	Meaning	Typical cause
#N/A	#NV	Value not available	VLOOKUP cannot find the lookup value
#DIV/0!	#DIV/0!	Division by zero	=B2/C2 when C2 is empty or 0
#VALUE!	#WERT!	Wrong data type	Text inside a calculation, such as ="abc"*2
#REF!	#BEZUG!	Invalid reference	A row or column the formula pointed to was deleted
#NUM!	#ZAHL!	Invalid number	=SQRT(-4)
#NAME?	#NAME?	Unknown name	A misspelled function, such as =SUMM(A1:A5)

For what causes each error and how to fix it rather than hide it, see Excel error values.

One more: division by zero

A cost sheet where one quantity has not been entered yet. Dividing by it breaks the formula until you catch the error.

Task In D2, calculate the cost per unit for the banners. They have not been delivered yet, so C2 contains 0. D2 should show 0 instead of #DIV/0!.

	A	B	C	D
1	Item	Cost $	Units	Cost per unit
2	Banners	480	0
3	Flyers	1250	500
4	Pens	360	120
5	Office chairs	2100	70
6	Notepads	96	40

IFNA: the more precise option

IFNA works like IFERROR but only reacts to #N/A: =IFNA(VLOOKUP(E2,A2:C7,3,FALSE),"not found"). For lookups this is usually the better pick. A missing lookup value gets a friendly message, while real problems such as a misspelled function name or a deleted range still show up as errors.

Why catching every error can backfire

An error value is often a useful alarm. If someone deletes a column, your VLOOKUP returns #REF!. Wrapped in IFERROR it says "not found" instead, and nobody notices the formula is broken. A 0 as fallback in totals is just as risky: the sum looks reasonable even though values are missing.

Only catch errors you expect, choose a fallback that is easy to spot, and add IFERROR after the formula already works without it.

IFERROR with VLOOKUP, or XLOOKUP instead

The classic use case is a VLOOKUP where missing values are normal, for example an order list that contains new items. With Microsoft 365, Excel 2021 or Excel 2024 you can skip the nesting: XLOOKUP has a fourth argument for exactly this, =XLOOKUP(E2,A2:A7,C2:C7,"not found"). It only applies when the value is missing, so it behaves like IFNA.

Common IFERROR mistakes

Quoting a number

=IFERROR(B2/C2,"0") returns the text 0, not a number. Functions like SUM skip text like that. Type numbers without quotes.

Empty text breaks later formulas

A fallback of "" looks blank but is text. A follow-up formula like =D2*2 returns #VALUE!, although SUM simply ignores it.

Wrong results are not errors

IFERROR only reacts to error values. A VLOOKUP without FALSE on unsorted data often returns a wrong value instead of an error, and IFERROR lets that through.

IFERROR in German Excel

German Excel calls IFERROR WENNFEHLER and IFNA WENNNV. Arguments are separated by semicolons:

English	German
=IFERROR(B2/C2,0)	=WENNFEHLER(B2/C2;0)
=IFNA(VLOOKUP(E2,A2:C7,3,FALSE),"not found")	=WENNNV(SVERWEIS(E2;A2:C7;3;FALSCH);"nicht gefunden")

The German error names are in the table above. Our exercises accept both spellings.

Which Excel versions have IFERROR?

IFERROR is in every current version, including Excel for Mac and Excel for the web. IFNA arrived with Excel 2013. Older workbooks sometimes still use =IF(ISERROR(…),…,…), the predecessor.

FAQ