IFNA function in Excel

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:

=IFNA(Value, Value_if_NA)

Let’s see an example to understand this.

1

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

=IFNA(VLOOKUP(“Jim”,A2:B6,2,FALSE),”Not Found”)

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.

2

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

=IFNA(VLOOKUP(“Mary”,A2:B6,2,FALSE),”Not Found”)

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.

3

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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube