IFERROR and IFNA functions in Google Sheets

IFERROR returns a value of your choice when a formula gives an error; IFNA does so only for the #N/A error.

Some errors are not mistakes. A code that is not in the list, a month with no sales yet, an import still loading: each produces an error value where a calm blank or a short note would serve the reader better. IFERROR supplies that.

Syntax

=IFERROR(value, [value_if_error])

=IFNA(value, value_if_na)

Argument	What it is
`value`	The formula to try.
`value_if_error`	What to show if it gives an error. In Sheets this may be left out, and the result is then blank.

Examples

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, 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.
=IFERROR(D2/E5,0)
Result: 0
Dividing by zero is turned into 0.
=IFERROR(D2/E5)
Result: (blank)
With no second argument, Sheets returns an empty cell.
=IFNA(VLOOKUP("Z999",A2:E8,2,FALSE),"Not found")
Result: Not found
IFNA catches the "not found" error.
=IFNA(D2/E5,0)
Result: #DIV/0!
IFNA leaves other errors alone, so a real problem still shows.

How it works

IFERROR works out its first argument. If the result is fine, that is what you see. If it is any error, you see the second argument instead.

In Google Sheets the second argument is optional. =IFERROR(formula) returns a blank on error, which is a short way to hide errors in a display column.

Where Sheets users need it most

FILTER returns #N/A when no row matches, and has no argument for a message: =IFERROR(FILTER(...), "None").
IMPORTRANGE, IMPORTHTML and GOOGLEFINANCE show errors while loading or when a source is unavailable.
REGEXEXTRACT returns #N/A when the pattern is not found.
VLOOKUP for a missing key. XLOOKUP has its own "missing" argument.
Division where the divisor may be zero or blank.

It hides everything

IFERROR does not ask why the formula failed. A misspelt function name, a range that no longer exists and a column number beyond the table are all hidden as thoroughly as a missing code. The sheet looks tidy and is wrong.

The errors it covers:

Error	Usual meaning
`#N/A`	value not found
`#DIV/0!`	division by zero
`#VALUE!`	wrong kind of data
`#REF!`	a reference that does not exist
`#NAME?`	unknown function or name
`#NUM!`	impossible number
`#ERROR!`	the formula could not be read at all

Only the first two are normally expected.

Prefer IFNA for lookups

IFNA replaces #N/A only. A missing key gets your message. Any other error stays visible, so you find out about real faults. The last two examples show the difference.

Good habits

Get the formula right without the wrapper first.
Add IFERROR or IFNA last.
Choose the replacement with care. A 0 flows into totals as if it were data. A blank or a word such as "Not found" does not.
For division, test the cause directly: =IF(E5=0, "", D2/E5).

With ARRAYFORMULA

IFERROR works across a whole column: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, Prices!A:D, 4, FALSE))) leaves a blank on every row where the code is missing, including the empty rows at the bottom. That makes the usual IF(A2:A="", "", ...) guard unnecessary in this particular case.

Common mistakes

Wrapping a formula that has a real fault and never noticing.
Using IFERROR where IFNA is the honest choice.
A numeric replacement that distorts averages and counts.
Stacking several IFERRORs to try one lookup after another, which becomes slow on large sheets.

In Excel

IFERROR and IFNA exist in Excel. There, IFERROR's second argument is required.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

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