The Excel IFNA Function

Related Function:
IFERROR

Function Description

The Excel IFNA function tests if an initial supplied value (or expression) evaluates to the Excel #N/A error. If so, the function returns a second supplied value; Otherwise the function returns the first supplied value.

The syntax of the function is:

IFNA( value, value_if_na )

Where the arguments are as follows:

value-The value or expression to be tested.
value_if_na-The alternative value or expression, that is to be returned if the first supplied value evaluates to the #N/A error.

Note: the IFNA function is new to Excel 2013, so is not available in earlier versions of Excel.


IFNA Function Examples

Example 1

The following spreadsheet shows two simple examples of the Excel IFNA function:

 Formulas:
 AB
155=IFNA( A1, "not found" )
2#N/A=IFNA( A2, "not found" )
 Results:
 AB
15555
2#N/Anot found

Example 2

In cells D1 and D2 of the following spreadsheet, the Excel IFNA function is used to check the result of the Excel Vlookup Function.

 Formulas:
 ABCD
1Lookup ListJim's Class:=IFNA( VLOOKUP( "Jim", A2:B6, 2, FALSE ), "not found" )
2BethClass 1Mary's Class:=IFNA( VLOOKUP( "Mary", A2:B6, 2, FALSE ), "not found" )
3BobClass 2  
4AlfClass 2  
5JimClass 3  
6AnnClass 3  
 Results:
 ABCD
1Lookup ListJim's Class:Class 3
2BethClass 1Mary's Class:not found
3BobClass 2  
4AlfClass 2  
5JimClass 3  
6AnnClass 3  

For further information and examples of the Excel IFNA function, see the Microsoft Office website.