IFERROR and IFNA functions in Excel

IFERROR returns a value you choose when a formula gives an error, and the formula's own result when it does not.

A sheet full of #N/A and #DIV/0! looks broken even when nothing is wrong: the code simply was not in the list, or the month has no sales yet. IFERROR lets you decide what the reader sees in those cases.

Syntax

=IFERROR(value, value_if_error)

=IFNA(value, value_if_na)

Argument	What it is
`value`	The formula or value to check.
`value_if_error`	What to show if the formula gives any error.
`value_if_na`	For IFNA: what to show if the formula gives the #N/A error only.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

=IFERROR(VLOOKUP("Z999",A2:E8,2,FALSE),"Not found")
Result: Not found
The lookup fails with #N/A, so the message is shown.
=IFERROR(VLOOKUP("A102",A2:E8,2,FALSE),"Not found")
Result: Toor Dal 1 kg
The lookup works, so its own result is shown and the message is ignored.
=IFERROR(D2/E5,0)
Result: 0
E5 is 0, and dividing by zero gives #DIV/0!. IFERROR turns it into 0.
=IFNA(VLOOKUP("Z999",A2:E8,2,FALSE),"Not found")
Result: Not found
IFNA catches the "not found" error in the same way.
=IFNA(D2/E5,0)
Result: #DIV/0!
IFNA catches only #N/A. A division by zero is a different error, so it is still shown.
=IFERROR(VLOOKUP("A102",A2:E8,9,FALSE),"Not found")
Result: Not found
A real mistake hidden: column 9 does not exist in a 5-column table. The true error is #REF!, but the cell says "Not found".

How it works

IFERROR works out the formula in its first argument. If the result is fine, that result is shown. If it is any error value, the second argument is shown instead. The formula is written once, which is the improvement over the older pattern =IF(ISERROR(formula), "message", formula).

The replacement can be anything:

a message, such as "Not found";
a zero, when the result feeds other sums;
empty text "", to leave the cell looking blank;
another formula, such as a second lookup in a different table.

A second lookup as the fallback

=IFERROR(VLOOKUP(A2,ThisYear,2,FALSE), VLOOKUP(A2,LastYear,2,FALSE)) tries one table and, if the code is not there, the other. You can chain three or four tables this way.

IFERROR hides every error, including your mistakes

This is the danger, and the last example shows it. The formula asks for column 9 of a five-column table. That is a typing mistake, and Excel would normally say #REF!. Wrapped in IFERROR, the cell calmly says "Not found", and the mistake may never be noticed.

IFERROR catches all of these:

Error	Usual meaning
`#N/A`	a lookup found nothing
`#DIV/0!`	division by zero or by an empty cell
`#VALUE!`	text where a number was needed
`#REF!`	a reference to cells that do not exist
`#NAME?`	a misspelt function or name
`#NUM!`	a number that is impossible or too large

Only the first two are normally "expected". The rest mean the formula itself is wrong.

Prefer IFNA for lookups

IFNA catches #N/A and nothing else. For a lookup that is what you want: a missing code is handled, and a real mistake still shows as an error so that you can fix it. Compare the fourth and fifth examples. IFNA is in Excel 2013 and later.

XLOOKUP has the same protection built in, as its fourth argument.

Good habits

Build the formula without IFERROR first, and make sure it gives right answers.
Add the wrapper last.
Use IFNA for lookups and a plain test for division: =IF(E5=0,0,D2/E5) says exactly which case you are handling.
Think about what the replacement means. A 0 for a missing price will flow into totals as if the item were free. Sometimes a visible "Not found" is the honest answer.

In Google Sheets

Google Sheets has IFERROR and IFNA. There, the second argument of IFERROR is optional and the result is blank if you leave it out.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.