Excel IFNA Function

The TRUE function is a function in Excel which is grouped under Logical function. This function can be used as a worksheet function (WS) which means it can be fixed into a formula in a cell of a worksheet.

What is the Excel IFNA Function?

Whenever the formula returns the #N/A error, the Excel IFNA function is used to replace that error with the value you specify as the result.

The Excel IFNA function can only be used to catch the #N/A errors and not responsible for any other type of errors.

Formula or Syntax

=IFNA(value,value_if_na)

Arguments or Parameters

The Excel IFNA function formula has the following parameters:

  • Value (required) – The value or formula to check for the #N/A error value.
  • Value_if_na (required) – The value to return if the #N/A error occurs.

How to use the Excel IFNA Function?

The Excel IFNA function is used to trap and handle errors in formulas, particularly those that perform lookups such as VLOOKUP,  or MATCH, or HLOOKUP, etc.

Let us consider the example below for better understanding.

  • Suppose we bought certain quantities of different products from a store and they are presented in a table.

Figure 1. Table showing different products to illustrate the IFNA function.

  • The VLOOKUP function will be used here to check for the quantity of a product. The value parameter of the IFNA function is the VLOOKUP function in this case.

Figure 2. The IFNA function showing the VLOOKUP function as the ”value” parameter.

  • If the product is not available, the “value_if_na” value will be used to replace the #N/A error. “None” is used in this case.

Figure 3. The IFNA function showing the “value_if_na” parameter.

  • Press Enter and the VLOOKUP function looks up for the quantity. If found, the result is returned and if not, the IFNA function returns “None”.

Figure 4. The quantity of the product was found and returned.

  • This other product used was not available. Therefore, the IFNA function returns “None”.

Figure 5. The IFNA function replaces the #N/A error with the “None” value.

Notes

  • If the value is an empty cell, the IFNA does not see it as an error but treats it as an empty string (” “).
  • When an error occurs, and the value_if_na is an empty string (” “), no message is displayed.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar