Quick Answer

IFNA Function

Replaces #N/A errors with a fallback value.

Syntax

IFNA

(value, value_if_na)

Parameters

Parameter	Description	Required
value	The expression, formula, or cell to evaluate.	Required
value_if_na	The value returned if value evaluates to #N/A.	Required

Basic Example

Catch a missing lookup

=IFNA(VLOOKUP("Z",A1:B5,2,FALSE),"Not found")

ResultNot found

When the lookup finds no match it returns #N/A, which IFNA replaces with "Not found".

Advanced Examples

Example 1: Return zero instead of #N/A

A lookup may miss

Use a numeric fallback for math

=IFNA(VLOOKUP(A1,Table,2,FALSE),0)

Result: 0 when no match

This lets downstream math proceed without breaking on #N/A.

Example 2: IFNA vs IFERROR

A formula might return #DIV/0!

IFNA leaves other errors untouched

=IFNA(1/0,"NA only")

Result: #DIV/0!

Because the error is #DIV/0! (not #N/A), IFNA does not catch it and the error passes through.

How IFNA Works

IFNA evaluates value first; if the result is the #N/A error it returns value_if_na, otherwise it returns the original result. It deliberately ignores all other error types.

1

Write the formula

Place the expression that might return #N/A as the first argument.

2

Add the fallback

Provide the value to show when #N/A occurs.

3

Enter IFNA

Type =IFNA(value, fallback) and press Enter.

Important Notes & Limitations

Only catches #N/A; use IFERROR to catch all error types.
Available in Excel 2013 and later.
If value_if_na is itself an error, that error is returned.

Common Errors & Fixes

Other errors not caughtIFNA only handles #N/A, so #VALUE!, #DIV/0!, etc. pass through.

Fix: Use IFERROR if you need to catch every error type.

Download Practice File

Practice IFNA with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the IFNA function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

AND

AND checks if ALL conditions are TRUE.

FALSE

Returns the logical value FALSE.

IF

IF checks a condition and returns different values based on whether it is true or false.

IFERROR

IFERROR catches errors and returns a custom value instead.

IFS

A simpler way to write multiple IF statements without nesting.

NOT

NOT flips TRUE to FALSE and FALSE to TRUE.

Frequently Asked Questions

IFNA vs IFERROR?

IFNA catches only #N/A; IFERROR catches all error types. IFNA is safer when you only expect missing values.

Which Excel versions have IFNA?

Excel 2013 and later, plus Microsoft 365.

What happens to non-#N/A errors?

They are returned unchanged, not replaced.