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 )
2. Click on the fx icon (or press shift+F3)
3. In the insert function tab you will see all functions
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
=IFNA(VLOOKUP(A2,A5:D14,2,FALSE),"Not Found") ----->>>>answer is emptyExamples 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 emptyExample2:
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 availableExample3:
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 availableExample4:
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 180Example5:
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.
