Exceljet

Quick, clean, and to the point

Excel IFNA Function

Excel IFNA function
Summary 

The Excel IFNA function returns a custom result when a formula generates the #N/A error, and a standard result when no error is detected. IFNA is an elegant way to trap and handle #N/A errors specifically without catching other errors.

Purpose 
Trap and handle #N/A errors
Return value 
The value supplied for #N/A errors
Syntax 
=IFNA (value, value_if_na)
Arguments 
  • value - The value, reference, or formula to check for an error.
  • value_if_na - The value to return if #N/A error is found.
Version 
Usage notes 

Use the IFNA function to trap and handle #N/A errors that may arise in formulas, especially those that perform lookups using MATCH, VLOOKUP, HLOOKUP, etc. The IFNA function will only handle #N/A errors, which means other errors that may be generated by a formula will still display.

You can also use the IFERROR function to catch the #N/A errors, but IFERROR will also catch other errors as well. 

IFNA with VLOOKUP

A typical example of IFNA used to trap #N/A errors with VLOOKUP will look like this:

=IFNA(VLOOKUP(A1,table,2,0),"Not found")

Notes:

  • If value is empty, it is evaluated as an empty string ("") and not an error.
  • If value_if_na is supplied as an empty string (""), no message is displayed when an error is detected.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.