# IFNA function in Excel

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:

=IFNA(Value, Value_if_NA)

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.

## Users are saying about us...

1. aurino djamaris

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

2. 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.