The Excel IFNA function lets you display a custom (user-friendly) message if the formula returns a #N/A error. Such an error is common when the formula cannot identify or find something within the given array or range.
For example, suppose that you have the two tables as shown in the following image.
The first one includes last month’s sales, and the second one includes a list of products in your shop. Now, your task is to add sales figures to the second table.
Our VLOOKUP formula would be as follows:
=VLOOKUP(E3, $B$2:$C$8, 2, 0)Now, as you can see in the following image, we are getting a #N/A error for the products that were not sold last month.
We wish to get rid of that error. Let us replace that error with a custom message, “No Demand”. Here’s how the IFNA function can help:
=IFNA(VLOOKUP(E3, $B$2:$C$8, 2, 0), "No Demand")Where,
- VLOOKUP(E3, $B$2:$C$8, 2, 0) is the formula to fetch the sales figures
- No Demand is the desired message if the sales was 0 in the previous month
The results are displayed in the following GIF.
Syntax
The IFNA is a simple logical function with the following syntax. It is mostly combined with the lookup functions such as VLOOKUP, HLOOKUP, and MATCH to get rid of the #N/A error.
=IFNA(value, value_if_na)Where,
- value argument needs to be replaced with the formula or expression, which may return #N/A error if the desired values are not found in the given array or range
- value_if_na argument needs to be replaced with the custom message
Important Notes:
- The function catches only the #N/A error.
- It is not case-sensitive.
- You can put a text, number, formula, and even another function in place of the second argument.
- If the second argument is empty, the function returns an empty string value (“”).
How to use the IFNA Function in Excel
Download the example spreadsheet used to demonstrate the IFNA function in the upcoming section. Practice is the key to success!
Example 1: IFNA Function Basics
Let us assume that you are a Sales Manager handling a huge team of 100+ members. At the end of every month, your team members ask you about the bonus they will get. So, you wish to create a spreadsheet where your team members can enter their names and know if they received a bonus or not.
Refer to the following image:
We are going to use the VLOOKUP function to fetch the bonus from the main table. But the problem is we don’t have all the team members’ names in the source table. We only have those who got the bonus.
So, when we use the VLOOKUP formula to get the bonus, we will get a #N/A error for the employees who are not on the list.
Our formula is as follows:
=VLOOKUP(F2, B:D, 3, 0)Where,
- F2 is the cell where team members can enter their names to know if they received a bonus.
- B3 is the column number that includes the bonus.
- 3 is the column number that includes the bonus.
- 0 ensures an exact match.
If we specify “Elena Vang”, who is in the team but didn’t get any bonus, the function returns an error as shown in the following image.
Now, the team members can interpret the #N/A error as no bonus for them in the given month. But we want something specific. Let us display the message “No Bonus” if the team member didn’t get any bonus.
We will use the IF function to catch and replace the #N/A error.
Here are the steps,
- Select the cell G2
- Type =IFNA
- Double-click the IFNA command
- Put the formula =VLOOKUP(F2, B:D, 3, 0)
- Type , to move to the next argument
- Specify “No Bonus” if the formula won’t find the person’s name in the given list
- Close the bracket for the IFNA function using )
- Hit the Enter key
Our formula is as follows:
=IFNA(VLOOKUP(F2, B:D, 3, 0), "No Bonus")Where,
- VLOOKUP(F2, B:D, 3, 0) is the core formula to find the employee name in the source table and display his/her bonus.
- No Bonus is the desired output if the VLOOKUP formula fails to find the given name in the source table.
The results are displayed in the following GIF.
When to use? – IFERROR vs IFNA
The IFNA is a logical function that can catch a #N/A error generated by lookup formulas (VLOOKUP, HLOOKUP, MATCH, etc.).
On the other hand, the IFERROR function catches all types of errors in Excel. It is an advanced function which is particularly helpful in the case of complex formulas that can return multiple errors.
For example,
- #N/A
- #NAME
- #VALUE
- #REF
- #DIV/0
- #NUM
The IFERROR function has a similar syntax:
=IFERROR(value, value_if_error)Where,
- value argument needs to be replaced with the value, formula, cell reference, etc.
- value_if_error argument lets you specify a custom value if an error is found
To learn more about the IFERROR function, click here: Learn IFFERROR Function in Excel (Formula, Syntax, Examples)
Takeaway
The IFNA function helps you simplify the outputs generated by the lookup functions. It is mostly combined with the VLOOKUP to prepare easy-to-understand tables, reports, dashboards, etc.
The function is quicker when it comes to troubleshooting #N/A error. It has a simple syntax. Anyone can learn it without any prior spreadsheet experience. I hope this article taught you all the bells and whistles of the IFNA function.
Please comment below if you are stuck or encounter any particular error while using the IFNA function in Excel. I will answer your questions as soon as possible.
Additional Resources:
- IFERROR Function in Excel
- ISERR Function in Excel
- ISERROR Function in Excel
- ERROR.TYPE Function in Excel
- Types of Errors in Excel
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.