The IFNA function is primarily used to handle the #N/A errors. It returns a specific value as per your instruction if such #N/A error occurs; otherwise, it returns the absolute value of the function. In this article, we’ve discussed the IFNA function in Excel in detail with 2 suitable examples.
We will be using the following product price list as our demo dataset to demonstrate all the examples regarding the IFNA function. Now let’s have a sneak peek of our dataset:
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
Introduction to the IFNA Function
- Function Objective:
The IFNA function is used to tackle the #N/A error.
- Arguments Explanation:
|value||Required||Value is to check for the @N/A error.|
|value_if_na||Required||Value to return only if the #N/A error is found.|
- Return Parameter:
Value of the first argument or an alternative text.
2 Examples to Use the IFNA Function in Excel
1. Basic Usage of the IFNA Function in Excel
In this example, we will show you the very basic usage of the IFNA function. As we have already mentioned the syntax of the IFNA function that is, IFNA(value, value_if_na).
So if there’s any valid value available in the value field, then that value will appear as a function output. Otherwise, the value_if_na field will return its specified value as a function output.
In the image below, there’s already #N/A within cell D14. So if we refer to cell D14 within the value field of the IFNA function, then the value specified in the value_if_na field will appear in cell D15. Now insert the formula within cell D15,
As we press the ENTER button, we can see the Missing message appear within cell D15 as predicted.
Related Content: How to Use IF Function in Excel (8 Suitable Examples)
2. Usage of the IFNA Function with the VLOOKUP Function
First of all, we want to demonstrate the usability of the IFNA function with the VLOOKUP function. This is the most common usage of the IFNA function.
You may want to use the VLOOKUP function to extract values based on a lookup value. Now what’s inconvenient about the VLOOKUP function is that it has a complex syntax as well as it requires a bundle of rules to follow to work properly.
So by any means, if you do any of the mistakes, then the VLOOKUP will show the #N/A error. Which is nothing but an error that represents, value not available.
Now, suppose you don’t want to allow the #N/A message throughout your dataset. But interested in showing a more meaningful message. In that case, you can use the IFNA function along with the VLOOKUP function to tackle the error message in a better way.
Let’s say for any #N/A error message, we want to show “Missing”. In the image below, we can see the #N/A message within cell D15.
The formula within cell D15 is:
If we look closely at the data table below, we can see that the lookup value is Cereal. But there is no such value in the first column of the data table. As the result #N/A error is showing there.
Now if we want to show Missing in replace of #N/A, then we have the use the following formula with IFNA function.
This is how we can use the IFNA function along with the VLOOKUP function.
␥ Formula Breakdown
- D14 ▶ stores the lookup value.
- B5:D12 ▶ table lookup array.
- 3 ▶ column index.
- 0 ▶ specifies exact match.
- VLOOKUP(D14,B5:D12,3,0) ▶ look for Cereal and returns its corresponding price.
- =IFNA(VLOOKUP(D14,B5:D12,3,0),”Missing”) ▶ returns the value of VLOOKUP(D14,B5:D12,3,0) is lookup value if found within the first column otherwise returns Missing within cell D15.
- How to Use TRUE Function in Excel (With 10 Examples)
- Use FALSE Function in Excel (With 5 Easy Examples)
- How to Use Excel SWITCH Function (5 Examples)
- Use Excel XOR Function (5 Suitable Examples)
IFERROR Vs IFNA Function
The IFERROR function handles a wide range of errors whereas the IFNA function tackles only the #N/A i.e. not available error.
For instance, if there’s any typo in your formulas then Excel may return the #NAME error. In this case, the IFERROR function can handle the error by showing an alternating text in replace of the #NAME message.
On the other hand, the IFNA cares only about the #N/A function. This can display an alternative text in replacing the #N/A error showing.
So, if you want to handle only the #N/A error, then it’s the best practice to use the IFNA function in lieu of the IFERROR function. For the other types of errors, you can use the IFERROR function.
Things to Remember
📌 If a cell is empty, then it’s treated as an empty string (“”) but not as an error.
📌 If you don’t fill up the value_if_na field, then the IFNA function will consider this field as an empty string value (“”).
To sum up, we have discussed every possible aspect with corresponding examples regarding the Excel IFNA function. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.