IFNA function in Excel

What is IFNA function in Excel?

The IFNA function is one of the Logical functions of Excel.

It returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression.

We can find this function in the Logical of the insert function Tab.

How to use IFNA function in excel

1. Click on an empty cell (like F5 )

empty cell excel

2. Click on the fx icon (or press shift+F3)

Click on the fx icon excel

3. In the insert function tab you will see all functions

 insert function tab excel

4. Select Logical category

5. Select IFNA function

6. Then select ok

7. In the function arguments Tab you will see the IFNA function

8. In Value section we can enter any value or expression or reference

9. In Value_if_na section we can enter any value or expression or reference

10. You will see the result in formula result section

How to use IFNA function in excel
=IFNA(VLOOKUP(A2,A5:D14,2,FALSE),"Not Found") ----->>>>answer is  empty

Examples of IFNA function in excel

Example1:

How to find #NA Error and replace it with an empty cell in excel?

by IFNA function, we can find #NA error.#N/A is the error value that means “no value is available.” Use NA to mark empty cells.

=IFNA(VLOOKUP(A2,A5:D14,2,FALSE),"") ----->>>>answer is  empty

Example2:

How to add IFNA to VLOOKUP?

VLOOKUP function, if the data is not available in the database, by default, it cannot display a sentence in the output and it shows the #NA error . As a result, you should always use IFNA function with VLOOKUP function.

=IFNA(VLOOKUP(A2,A5:D14,2,FALSE),"not available") ----->>>>answer is  not available

Example3:

How to add IFNA to HLOOKUP?

HLOOKUP function, if the data is not available in the database, by default, it cannot display a sentence in the output and it shows the #NA error. As a result, you should always use IFNA function with HLOOKUP function.

=IFNA(HLOOKUP(A2,A5:D14,2,FALSE),"not available") ----->>>>answer is  not available

Example4:

How to add IFNA to XLOOKUP?

There is no need to use these two functions together because the XLOOKUP function includes it in its third argument.

=XLOOKUP(A2,A5:A14,D5:D14,FALSE)----->>>>answer is 180

Example5:

Is IFNA the same as IFERROR?

the IFERROR function checks the following errors and changes the output to the specified value if found: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

the IFNA function just checks the #N/A error and changes the output to the specified value if found.

IFERROR#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!
IFNA#N/A

What is the purpose of IFNA function?

It returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression.

What is the Return value  of IFNA function?

It can return any type of data (number, text,….).

=IFNA(Value section,Value_if_na section)

How many arguments does IFNA function have?

=IFNA(Value section,Value_if_na section)

This function has just 2 Argument.

The first argument of IFNA function is Value section.

The second argument of IFNA function is Value_if_na section.

The arguments of IFNA function are required and not optional.

Which version of excel supports IFNA function?

This function is available for all excel versions (2003-2019)

Errors in IFNA function

This function never gives an error in the output.

IFNA related functions

Leave a Reply

Your email address will not be published. Required fields are marked *