In this article, we will learn about the IFNA function.
IFNA is a built-in function in Excel. It is categorized as a Logical Function on logic_test. It traps and holds the #NA error and returns the value if error occurs.
IFNA function holds the #NA error and returns value_if_NA error occurs.
Syntax:
Let’s see an example to understand this.
Here we have a list of 5 students with class.
We will look up the class of the student from name using LOOKUP function.
Use this formula to find the Jim’s class
Explanation:
VLOOKUP function looks for the name Jim in the list.
IFNA function works if #NA error occurs and returns Not found if #NA error occurs else returns class of Jim.
It returns Class 1 as Jim’s class.
Now we try to find Mary’s class. Some of you would be wondering Name of Mary is not in the list. Exactly, So LOOKUP function will return #NA error and IFNA function comes in use here.
Use the formula to find Mary’s class
Explanation:
VLOOKUP function looks for the name Mary in the list.
IFNA function works if #NA error occurs and returns Not found if #NA error.
As you can see IFNA holds the #NA error and returns Not found.
Hope you understood how to use the IFNA function. You can use the same functions in Excel 2016, 2013 and 2010. There are more articles on Logic_test. Please do check more links here and if you have any unresolved query, please state that in the comment box below. We will help you.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Ed J... According to Microsoft's own support page the IFNA function wasn't introduced until Excel 2013 (2011 for Mac). You can use IFERROR in its place in Excel 2010.
IFNA:
https://support.office.com/en-US/article/IFNA-function-6626C961-A569-42FC-A49D-79B4951FD461
IFERROR:
https://support.office.com/en-US/article/IFERROR-function-C526FD07-CAEB-47B8-8BB6-63F3E417F611
Aurino,
I attempted your code, as I am using Excel 2010 and would really like to be able to use the ifna function. But I get the following error:
Compile error:
Sub or Function not defined.
Do I use it still like a regular function? My formula is:
=ifna(MATCH(I3,JUN!$D$4:$R$4,0),1)
Where as if I run =MATCH(I3,JUN!$D$4:$R$4,0)it returns: #N/A.
To add your code, I went to VBA, then insert menu, and chose 'insert module' and pasted your code in that box.
When I go to add the function to the cell, the function Ifna shows up as an available option (which I didn't have before).
Am I doing something wrong? After the error, and I hit the "OK" button, It highlights and puts an yellow arrow on the first two lines of your code.
Any help you can provide would be appreciated. I wish I had excel 2013 so I just had this function with the software. I could really use this function for my project.
If you are using Excel 2010 or earlier versions, then you can use IFNa function (value, value_if_na) with VBA code as follows:
Public Function Ifna(ByRef vTest As Variant, _
Optional vDefault As Variant = vbNullString) As Variant
'
'Aurino Djamaris - 2013
'
Ifna = vTest
On Error GoTo out
If vTest = CVErr(xlErrNA) Then
Ifna = vDefault
End If
out:
End Function
Good Luck