IFNA Function

🎯Purpose

Returns the value you specify if a formula returns the #N/A error value; otherwise returns the result of the formula. Specifically tailored for lookup functions where #N/A denotes a missing record rather than a mathematical bug.

Syntax

EXCEL

=IFNA(value, value_if_na)

Arguments Breakdown

Argument	Required?	Description
`value`	Required	The argument that is checked for the `#N/A` error value.
`value_if_na`	Required	The value to return if the formula evaluates to the `#N/A` error value.

Practical Examples

1. Graceful Missing Lookup Handling

From Formulas_&_Functions_Part_2.xlsx (Sheet Logical ):

EXCEL

=IFNA(VLOOKUP(LookupKey, MasterTable, 2, FALSE), "Not in System")

Why Prefer IFNA over IFERROR?

IFERROR catches all errors indiscriminately (including syntax typos like #NAME?, division by zero #DIV/0!, or deleted columns #REF!), masking genuine bugs.
IFNA only catches #N/A (unmatched key), allowing critical structural errors like #REF! to remain visible so you can fix them.

Related Knowledge

Notes: 03 Conditional Logic and Decision Making, 04 Lookup and Reference Functions
Workbook: 11_Demos_and_Workbooks/03_Formulas_and_Functions/Formulas_&_Functions_Part_2.xlsx (Sheet: Logical )