How to use the IFNA Function

Description

The IFNA Function is categorized as Logical Function in Excel. This function returns the value you specify if the formula with in the IFNA function returns #N/A errors. This is simple way handle #N/A errors.

The IFNA function takes two argument first one is value and 2nd one is value_if_na. It can also be used as a part of a formula.

Syntax

The syntax of IFNA function is =IFNA(value,value_if_na)

Argument or Parameter

value (Required)- The formula or expression to be checked for an #N/A error.

value_if_na (Required) - The value or message to be shown if formula or expression provides #N/A error.

If the num_chars is omitted, it will assume it as 1.
If the num_chars is 0, then it will return blank.
If the num_chars must be greater than 0 to return any text.

Returns

The RIGHT function returns specified number of character from the right most end of the given text string.

Example

Example-1

In the example, we have some data that is Item Name, Stock & Price. We need to find out the stock of a particular item. To solve this problem we used VLOOKUP function, =VLOOKUP(E4,$A$2:$C$7,2,0) and got result 25 for Hard Disk and #N/A error for UPS because we don't have UPS in database. To remove the error message with a custom message we need to use IFNA function =IFNA(VLOOKUP(H4,$A$2:$C$7,2,0),"Not Found"). Now for UPS we got the custom message Not Found.

Example-2

In this example we have same database but data are arranged horizontally, so if we need to find the price of the item. So we need to use HLOOKUP function, so type =HLOOKUP(A9,$B$2:$F$4,3,0). For Hard Disk we got 3000 but for UPS we got #N/A error. To solve this error and show custom message use the formula =IFNA(HLOOKUP(D8,$B$2:$F$4,3,0),"Out of Stock"). Now for error result we got the cust message Out of Stock.

Example-3

In this example, we same data and need to find the stock of item. Here we have used LOOKUP function to get the result. Use the formula =LOOKUP(E4,$A$2:$A$7,$B$2:$B$7). For Hard shows #N/A error and UPS as 15 because LOOKUP give approximate match. To solve the #N/A error use the formula =IFNA(LOOKUP(E4,$A$2:$A$7,$B$2:$B$7),""), Now you can see blank message for #N/A Erorrs.

Example-4

In this last example, we will perform the same job with MATCH function but here we want to know in which row of the database the item exist. Use the formula =MATCH(E13,A11:A16,0). For UPS we will get error message. In same way as we have done in previous example use IFNA Function =IFNA(MATCH(E13,A11:A16,0),"Not Found"), Now the result for UPS is Not Found.

So in this way you can use IFNA function to handle IFNA errors.