IFNA

New Excel function in Excel 2013

The IFNA function checks if a formula results in a #N/A error. If it doesn’t it returns the formula result, otherwise it returns another value.

It is similar to the IFERROR function. The IFERROR tests for the following errors #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL! The IFNA is more specific as it only checks for #N/A errors.

Syntax

IFNA(value, value_if_na)

The value is usually another function. The value_if_na is the value returned if the first value generates #NA It can be another formula, cell reference or value.

Comments

It is best combined with the VLOOKUP function to handle values that cannot be found. Other errors like #DIV/0 are returned rather than hidden.

Tip

IFNA can be used to replace existing formulas that combine the IF and ISNA functions

Examples