Related Function:

The IFNA function returns the value you specify if the formula returns the #N/A error value; otherwise returns the result of the formula.

  • This function was introduced in Excel 2013 and so is not available in earlier versions.

Syntax

=IFNA(value,value_if_na)

Arguments

Argument Description
value The argument that is checked for the #N/A error value
value_if_na The value to return if the formula evaluates to the #N/A error value
  • If value or value_if_na is an empty cell, IFNA treats it as an empty string value (“”)
  • If value is an array formula, IFNA returns an array of results for each cell in the range specified in value

Examples

  A B C D
1 Formula Result Notes
2 =IFNA(VLOOKUP(“Chicago”,$A6:$B11,2),”Not found”) 175 IFNA tests the result of the VLOOKUP function. Because Chicago is in the lookup range, VLOOKUP returns the Region ID value
3 =IFNA(VLOOKUP(“Seattle”,$A6:$B11,2),”Not found”) Not found IFNA tests the result of the VLOOKUP function. Because Seattle is not found in the lookup range, VLOOKUP returns the #N/A error value. IFNA returns the string “Not found” in the cell instead of the standard #N/A error value
4        
5 City Region ID    
6 Atlanta 105    
7 Portland 142    
8 Chicago 175    
9 Los Angeles 251    
10 Boise 266    
11 Cleveland 275