The Excel IFNA function is a logical function that returns the value specified if the expression resolves to #N/A, otherwise it returns the result of the expression. The purpose of this function is to handle #N/A results and show them in a custom manner instead of showing "#N/A".
By #N/A here, we mean 'not available' and IFNA literally means 'If not available'. The function made its entry with Excel 2013.
Syntax
The syntax of the IFNA function is as follows:
=IFNA(value, value_if_na)Arguments:
'value' – The value or expression to be tested for #N/A error.
'value_if_na' – The value to be returned when #N/A error is found.
Important Characteristics of IFNA Function in Excel
- The function will only deal with #N/A errors and not other errors.
- The function can deal with #N/A errors that come up with lookup functions.
- If the formula has any typos or misspelling, the function returns a #NAME?
Examples of IFNA Function
A few examples should clear up any confusion regarding the IFNA function. Let's take a look.
Example 1 – Simple IFNA function
Our example is a very plain application of the IFNA function. Let's have a look.
We have generated few different types of errors here to be checked by the IFNA function. We have used the IFNA function as:=IFNA(A2,"NA Error Found!")The function is set to find replace #N/A errors as "NA Error Found!" otherwise returns the value as-is. The IFNA function checks cell "A2", finds the #N/A error it was looking for, and returns "NA Error Found!" as the result.
Similarly, the function checks cell A3 for an #N/A error. Since it finds a #DIV/0! error instead of #N/A error, so the error is returned as-is.
This is basically how IFNA works. Let's see more practical examples.
Example 2 – IFNA function with VLOOKUP function
While looking up a value from a set of data, there is of course the possibility of not finding it. When trying to find that value using a lookup function (including VLOOKUP, HLOOKUP, XLOOKUP), the result is #N/A since the value is not available.
For our example, we have a small batch of 10 students and their class test marks in Psychology. We will try to find the marks of two students Zane Harlow and Sierra Bain via the VLOOKUP function. Let us show you:
Since no student with the name 'Sierra Bain' was present in our dataset, so the VLOOKUP function has returned a #NA error.
Now let's try to add the IFNA function into this formula and see the result:
So what happened here? We applied this formula:
=IFNA(VLOOKUP(E7,B2:C11,2,0),"Not Available")Since Zane Harlow (student no.7) was available in our set of data, the VLOOKUP function found the value present, picked up the corresponding marks from the 2nd column, and returned the value "19". No work for IFNA here.
Now to find the marks scored by Sierra Bain, VLOOKUP is told to find the data related to "E7" which is "Sierra Bain". The table given to VLOOKUP for searching is within the cell range "B2:C11"; VLOOKUP must find "E7" in the 1st column (column B) of the given table and return the corresponding value from the 2nd column (column C). "0" indicates that VLOOKUP must find an exact match. VLOOKUP could not find "Sierra Bain" in column B and results in a #N/A error.
This result is handled by the IFNA function and it turns "#N/A" into the text "Not Available" and displays it.
IFNA vs IFERROR Function
Much like IFNA but as the name suggests, IFERROR deals with a larger amount of Excel errors and returns a custom result if any error is detected. Otherwise, it returns the result of the stated function. While this may seem to be the answer to more problems, it may also unnecessarily cover-up for underlying problems without highlighting them and returning the custom result instead.
Let's bring back the previous example and see how this applies.
Here, we have used the IFERROR function instead of the IFNA function and made a spelling error in the name of the formula (VLOOKUP is misspelled as VLOOKP).
If you notice closely, Zane Harlow is very much there in the dataset, but still, the IFERROR function shows it as "Not Available". This is because the result of the VLOOKUP function results in a #NAME? error. The result is passed onto the IFERROR function where it covers up the error and returns the custom text "Not Available".
This doesn't truly reflect the scenario since the student's data is listed in the table.
On the other hand in this case, if we apply an IFNA function it returns the #NAME? error without hiding it.
So a truer representation comes from the IFNA function here which only works for #N/A errors and shows the underlying problem which is the #NAME? error, indicating a spelling error.
While being quite handy, IFERROR's shining moments are where you need to cover up any formula error and display a custom result.
How to Choose Between IFNA & IFERROR
In most cases, it would be more prudent to use the IFNA function with lookup functions, as it would display other errors indicating the relevant issue with the data or formula.
On the flip side, where more errors need to be handled, the IFERROR function would work better for customizing results. It has more usefulness in calculations. It will deal with a greater number of errors including the #N/A error and return the stated result instead.
IFNA vs ISNA Function
Narrowing the horizons of IFNA, the ISNA function (Is not available?) only checks for #N/A errors and returns TRUE or FALSE accordingly.
Both functions only check for #N/A errors.
- ISNA – returns TRUE/FALSE
- IFNA – returns the specified value
The ISNA function may be a suitable tool where the results are needed as TRUE/FALSE. However, if you're going for a specified result where you want to have control over the outcome, ISNA needs to be used with the IF function. Whereas, IFNA function alone can do the same thing.
Let's take another leaf out of the previous example to understand this.
Here the VLOOKUP function resulted in an #N/A error since the student 'Sierra Bain' is not present in our dataset. Nesting VLOOKUP function inside ISNA function changes "#N/A" to "TRUE". Next, in order to change "TRUE" to a value specified by us ("Not Available" in this case), we need to nest the VLOOKUP and ISNA functions into the IF function. The IF function will return "Not Available" if the nested formula returns "TRUE".
On the other hand, if the nested expression returns "FALSE" (that means #NA error is not found), so the result from the VLOOKUP function (supplied as the last argument) is returned.
Now, let's try and apply the IFNA function in this case.
Using IFNA gives the desired result without half as much of the fuss. So here, IFNA is a clean and clear winner.
It's been a good day for IFNA "if not" the best. Hope our tutorial made function applications relatable and easy. Speaking of which, how relatable is this?
We'll keep you linked to the Excel world. Have a good day!
About Mehwish Javaid