Excel IFNA Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 38 of 105 · Full Excel course

The Excel IFNA function evaluates a formula and returns a value you specify only when that formula produces the #N/A error; every other result, including other error types, is returned unchanged. IFNA in Excel is the precise way to tidy up VLOOKUP, MATCH and XLOOKUP results without hiding genuine mistakes such as #REF! or #VALUE!.

Table of Contents

IFNA syntax

=IFNA(value, value_if_na)

Arguments

Argument	Required	Meaning
value	Required	The formula or reference to check, typically a lookup.
value_if_na	Required	What to return when value is #N/A: text, a number, “” for a blank cell, or another formula.

IFNA was introduced in Excel 2013. In Excel 2010 and earlier use IF(ISNA(x), alternative, x).

Step-by-step example

=IFNA(VLOOKUP(C2, E:F, 2, FALSE), "Not found")

Excel runs the VLOOKUP first.
If C2 exists in column E, the matching value from column F is returned as normal.
If C2 is missing, VLOOKUP produces #N/A and IFNA replaces it with “Not found”.
If the VLOOKUP instead produces #REF! (for example, a column index of 3 on a two-column range), IFNA leaves the #REF! visible so you can fix the formula.

Practical use cases

1. Clean MATCH results

=IFNA(MATCH(G2, $A$2:$A$500, 0), "No match")

2. Zero instead of #N/A so totals still work

=IFNA(VLOOKUP(I2, B:C, 2, FALSE), 0)

3. Blank cell for reports

=IFNA(INDEX($C$2:$C$500, MATCH(H2, $A$2:$A$500, 0)), "")

4. Fall back to a second table

=IFNA(VLOOKUP(A2, Table1, 2, FALSE), VLOOKUP(A2, Table2, 2, FALSE))

5. XLOOKUP already has it built in

=XLOOKUP(A2, D:D, E:E, "Not found")

XLOOKUP’s fourth argument is an if_not_found value, so IFNA is rarely needed around it. Use IFNA when the workbook must also open in Excel 2013-2019.

IFNA vs IFERROR

IFERROR swallows every error type. That is convenient but dangerous: a broken range reference (#REF!), a misspelt function (#NAME?) or a division by zero all become “Not found”, and the real fault goes unnoticed. IFNA reacts only to #N/A, which in lookups simply means “the value is not in the list”. Use IFNA around lookups and IFERROR only when you deliberately want to suppress all errors.

Common mistakes and errors

Still seeing #REF!, #VALUE! or #DIV/0! – this is IFNA working as designed. Fix the underlying formula rather than switching to IFERROR.
#NAME? in old Excel – IFNA does not exist before Excel 2013. Use IF(ISNA(…)) for shared files.
Missing second argument – IFNA requires both value and value_if_na.
Text result in numeric columns – “Not found” makes SUMPRODUCT return #VALUE!; return 0 or “” where the column feeds calculations.
Wrapping the wrong part – IFNA(A2, 0)/B2 only guards A2. Wrap the whole expression whose result you want to protect.

Tips and best practices

Make IFNA your default wrapper for lookups and reserve IFERROR for formulas where every error is genuinely acceptable.
Return “” or 0 in calculation columns and a message only in presentation columns.
Check the Excel version of your audience; if anyone uses Excel 2010, use IF(ISNA()) instead.
Use the XLOOKUP if_not_found argument in Excel 365 and drop the wrapper entirely.
Count the replacements with COUNTIF(range, “Not found”) to monitor data quality over time.