Back in 2012, Excel authority Dick Kusleika asked the following:
Is IFERROR Bad?
The blog post at dailydoseofexcel.com went on to generate over 20 comments. Several Excel experts shared their opinions about Excel’s IFERROR function and, more generally, error checking in Excel formulas.
One of the shortcomings of the IFERROR function (at the same time, this is one of its strengths) is that it acts as a catch-all formula. This means that it covers several errors (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!). The consequence of this is that IFERROR isn’t really that helpful in helping you distinguish between the different types of errors and, sometimes, may hide a particular error that you’d like to fix.
In this post, I don’t repeat all the strengths and shortcomings of the IFERROR function. I cover this particular function in detail here. I will, instead, explain in detail a particular function that isn’t as widely used as IFERROR but, in some cases and for some users, is more appropriate:
The IFNA function.
More particularly, in this Excel tutorial we’ll cover the following material:
- 1 What Does The IFNA Function Do In Excel
- 2 Why The IFNA Function Is More Appropriate Than IFERROR In Some Circumstances
- 3 What Is An #N/A Error And What Causes It
- 4 Syntax Of The IFNA Function In Excel
- 5 How To Use The IFNA Function In Excel: An Example With The VLOOKUP Function
- 6 Why Using The IFNA Function Is More Appropriate Than Using IFERROR In The Example
- 7 Conclusion
- 8 Do you use the IFNA function? If you do, in which situations do you use it?
- 9 Books Referenced In This Excel Tutorial
Let’s start by understanding…
What Does The IFNA Function Do In Excel
The IFNA function is a very recent addition to Excel. The first version of Excel to include it was Excel 2013.
Excel includes the IFNA function in the group of logical functions. These are functions that use logical values (TRUE or FALSE) either as part of their input or return them as part of their output.
The 2 most popular logical functions are the IF function and the IFERROR function.
IFNA works in a very similar manner to the IFERROR function. Therefore, in order to understand what the IFNA function does, let’s take a quick look at what the IFERROR function does:
- #1: IFERROR checks a formula or expression in Excel.
- #2: If the formula or expression that is evaluated returns an error, IFERROR returns a value, or evaluate a formula or expression that you specify.
- #3: If the formula or expression checked doesn’t return an error, IFERROR returns the result of that formula or expression.
As explained in TechRepublic, the IFNA function follows a very similar process. More precisely, IFNA (i) evaluates a formula or expression, and (ii) returns one thing or another depending on whether that formula or expression returns or not the #N/A error. Therefore, the IFNA function proceeds as follows:
- #1: Checks a formula or expression.
- #2: If the formula or expression returns the #N/A error, IFNA returns a value, formula or expression that you get to specify.
- #3: If the formula or expression doesn’t return the #N/A error, IFNA returns the result of that formula or expression.
Let’s see how this looks graphically. Notice that the only substantial difference between this flow chart and that above (for the IFERROR function) is that it asks a more restricted question. Instead of checking whether the formula or expression returns any error, it restricts its scope to the #N/A error.
You may be wondering:
Why is the fact that IFNA only checks for the #N/A error such a big deal?
To answer that question, let’s go back to the IFERROR function…
Why The IFNA Function Is More Appropriate Than IFERROR In Some Circumstances
You already know that one of the main shortcomings of the IFERROR function is its breadth. Giving the same treatment to different errors is not appropriate in all circumstances and, in some cases, it increases the risks of mistakes in final Excel workbooks.
This is one of the reasons why you should consider using the IFERROR function only in output sheets and use it in a judicious and sparing manner.
As you’ve probably already realized, IFNA manages this weakness by being much more specific. It doesn’t treat all errors the same way. IFNA targets a very specific type of error: #N/A. All other possible errors, such as #DIV/0! or #REF!, are treated differently. As explained by IT consultant Susan Sales Harkins in TechRepublic:
This function isn’t superior to IFERROR(), but it is more specific about the type of error, which can lead to quicker troubleshooting.
In order to understand why this difference in the level of specificity is important for you, let’s take a look at an experts opinion:
Back in 2013, Microsoft MVP Jon Peltier stated that he never uses IFERROR. He went on to explain that he only used the following two error-trapping strategies:
- The IFNA function, although at that time this had to be structured using a combination of the IF and ISNA functions.
- An IF formula that tests whether a denominator is 0.
Peltier’s reasonable view is that:
Any other error needs immediate attention.
2 possible conclusions from the above are:
- Not all errors are created equal. I’ll explain why further below.
- Errors are not necessarily bad. They’re there to help you identify and fix problems in your Excel workbooks.
As you’ve probably concluded, it is advisable to use the IFNA function instead of the IFERROR function when the purpose of the formula is trapping and handling only #N/A errors.
In order to know when this is the case, you need to understand…
What Is An #N/A Error And What Causes It
As you probably already know, an #N/A (meaning not available) error looks as follows:
According to Microsoft, this is used by Excel to indicate “that a value is not available to a function or formula” and can be caused by any of the following:
- Missing data.
- #N/A or NA() has been entered in place of data.
Just to clarify, it’s actually a relatively common practice to enter #N/A or NA() in place of data in some situations. For example, as explained by computer author Paul McFedries in Excel 2013 Formulas and Functions, you may want to create #N/A errors on purpose in order to evidence that the data required to fill in a particular cell is not yet available.
- You’re using a reference function such as HLOOKUP, MATCH or VLOOKUP and you either:
#1: Give an inappropriate value for the lookup_value argument. This, as I’ll explain below, is one of the most common scenarios where using the IFNA function is advisable.
#2: Use the function to locate a value in a table that isn’t properly sorted.
I’ll explore all of these functions, and the lookup_value argument in a future tutorial. If you want to be updated by email as soon as I publish new tutorials in Power Spreadsheets, please enter your email address below.
- You (i) implement an array formula and (ii) use an argument that doesn’t have the same number of rows or columns as that of the range that has the formula.
I’ll also cover this topic more deeply in other blog posts.
- You omit arguments from a built-in or custom worksheet function.
- You try to use a custom worksheet function that is not available.
- You run a macro that returns #N/A.
Out of the above, and as explained by computer author Paul McFedries in Excel 2013 Formulas and Functions, the most common causes of #N/A errors are:
- You’re using an inappropriate argument.
- You’re omitting a required argument.
A very common cause of #N/A errors is using a reference function (such as VLOOKUP) and searching for values that aren’t in the data. In this scenario, searching for a value that isn’t in the data is interpreted by Excel as you providing an inappropriate value for the lookup_argument. Therefore, Excel returns the #N/A error.
Using the IFNA function along the VLOOKUP function is so common, that below I show you an example of this situation.
Now that you know what does the IFNA function does, why it is a better option than IFERROR for some situations and what is the type of error (#N/A) that it targets, let’s take a look at how you actually write a formula using the IFNA function.
Syntax Of The IFNA Function In Excel
The basic syntax of the IFNA function is relatively simple: “IFNA(value,value_if_na)”, where:
- “value” is a required argument indicating what is the formula or expression to be checked for an #N/A error.
The image below shows in which steps of the process followed by the IFNA function is the value argument important. As shown in this flow chart, value is also what the IFNA function returns if there is no #N/A error found.
- “value_if_na” is the value that Excel returns in case the formula or expression that is evaluated gives an #N/A error. This is also a required argument.
The following image shows again the process followed by the IFNA function and highlights where the value_if_na argument comes into play.
Microsoft explains 2 additional aspects to consider regarding the syntax of the IFNA function:
- If either of the arguments of the IFNA formula is an empty cell, the function treats such argument as the empty string value (“”) and usually returns the value 0.
- If the value argument is an array formula, IFNA returns an array of results for each of the cells in the range that was specified as an argument.
How To Use The IFNA Function In Excel: An Example With The VLOOKUP Function
If you’re a user of reference formulas such as VLOOKUP, you’ve probably seen your share of #N/A errors. In the words of famous Excel blogger Chandoo:
VLOOKUP is a powerful formula, alright. But ask it to look up a value that is not in the data and it acts up. So you need to handle this.
You don’t need to go too far to notice that this is true. A relatively simple Google search returned several threads at answers.microsoft.com that covered this subject, including the following:
- How to remove #N/A error in excel vlookup, because a value is not found.
- Removing the #N/A error message from vlookup.
- How to remove #n/a error in excel vlookup b/c value is not found.
- Modify a vlookup to not return a #NA.
- How do you replace Vlookup #NA errors with blank or zero?
Since the situation of VLOOKUP returning #N/A errors because it is not able to find values is a relatively common problem, let’s take a look at how you can use the IFNA function to handle this.
Sample Excel Workbook For VLOOKUP And IFNA Functions
For purposes of this Excel tutorial, I’ve created an Excel workbook that contains population and area data for 30 random cities. You can get this Excel file for free by clicking here.
In case you’re wondering from where I got the city names, I used this City & Town Name Generator.
Let’s assume that, from time to time, you need to get the data for a particular city to fill the following table:
You don’t want to be scrolling up or down all the time so you decide to use the VLOOKUP function to automate the process.
Introduction To The VLOOKUP Function
I’ll cover the VLOOKUP function in future tutorials. For the moment, is enough to know the following:
- You’re going to be using VLOOKUP for purposes of getting a city’s data using only its name. In other words, you want to simply type the city name and have Excel get the rest of the data (adult population, size and population density) for that city automatically.
- The basic syntax of the VLOOKUP function is “VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)”, where:
#1: “lookup_value” is the value that you are looking up. In the example above, lookup_value is the name of the relevant city.
#2: “table_array” is the cell range in which VLOOKUP should search for both the lookup_value and the value that it must return. In the case at hand, this is the table with the city data.
#3: “col_index_num” is the column number where the value that VLOOKUP must return is located. For the city data example, this argument is the number that appears in the first column as shown below:
Therefore, in the VLOOKUP formula that searches for the adult population, the col_index_num argument is 2. When looking for city size, col_index_num is 3 and, when searching for population density, is 4. Notice how these values correspond to the column numbers in the table that has all the data:
#4: “range_lookup” is the only optional argument. The range_lookup argument must be either TRUE or FALSE.
If you type “TRUE”, Excel assumes that the first column in the table is sorted alphabetically or numerically and searches for the closest match to the lookup_value. TRUE is also the default value so, if you don’t specify a range_lookup argument, Excel uses this method.
If you choose “FALSE”, Excel only searches for an exact match to the lookup_value in the first column of the chosen table_array.
Since the table in the sample Excel workbook is not sorted alphabetically nor numerically, and we only want exact matches, I’ve typed “FALSE”.
As an example, let’s take a look at the VLOOKUP formula used to return the adult population of any city. In the sample Excel worksheet, the formula that goes in cell I6 is “=VLOOKUP($I$5,$B$5:$E$34,G6,FALSE)”.
As you already know:
- The first argument ($I$5) makes reference to the city whose population we want Excel to return.
- The second argument ($B$5:$E$34) is the full data table.
- The third argument (G6) is the column number where the population data is stored within the table.
- “FALSE” makes reference to the fact that we want Excel to search for exact matches of the city name.
The following screenshot shows you a complete view of the full Excel worksheet and gives a better idea of the arguments.
The VLOOKUP formulas for the other 2 cells in the table that you want to fill (size and population density) are substantially the same. The only argument that needs to change is col_index_num, which is 3 when getting size data and 4 for population density data.
Once the results table is properly set up, we can simply type in the name of a city in cell I5. Excel should return the rest of the data.
You’ve probably noticed all the #N/A errors that appear when the city field is empty. Don’t worry about them for the moment. I’ll show you how to fix them later.
We’re now ready to test the formulas. If I type in the name of the first city in the full table (Tiltonsville), Excel returns the following data:
Which matches the data in the original table.
So the formula works!
However, let’s take a look at how to handle the #N/A errors that appear in the table sometimes.
The VLOOKUP Function And The #N/A Error: Why You Should Use The IFNA Function Along With VLOOKUP
You already know that one of the most common problems when using the VLOOKUP function is Excel returning the #N/A error. As explained by Microsoft, this is usually caused by either of the following:
- If the range_lookup argument is set to FALSE (VLOOKUP searches for exact matches), the #N/A error indicates that the particular value you’re searching for can’t be found. In other words, the value you’re looking for isn’t in the data.
- If the range_lookup argument is TRUE (VLOOKUP searches for the closest value in a table that is sorted numerically or alphabetically), #N/A is an indication that the value you’re looking up is smaller than the smallest value in the data table.
In the example above, we’ve set the range_lookup argument to FALSE. Therefore, the case that interests us is the first one, when the value that is being searched can’t be found in the data table.
Let’s take a look at how this looks in practice. I go back to the City & Town Name Generator, get the name of a city whose details are not in the Excel workbook I prepared previously and plug it in the sample Excel workbook (just as I did with Tiltonsville above). The following image shows what Excel returns:
This result isn’t unexpected. After all, I knew that I was typing a city that doesn’t appear in the data table that I originally created.
You may also remember that Excel displays the #N/A errors when the city field is empty.
How can you trap and handle these #N/A errors?
By using the IFNA function that I’ve explained in this Excel tutorial. More precisely, by nesting the VLOOKUP function that you’ve already built into a IFNA function.
Let’s see how this works in the case of the VLOOKUP formula that returns the adult population for a city. We’ll wrap the VLOOKUP function in a IFNA function that leaves the cell blank (“”) in case the VLOOKUP returns an #N/A error.
- The original formula, as explained above, is “=VLOOKUP($I$5,$B$5:$E$34,G6,FALSE)”.
- You already know that the basic syntax of the IFNA function is “IFNA(value,value_if_na)” and, in this particular case:
#1: “value” is the original VLOOKUP function, which is checked for #N/A errors.
#2: “value_if_na” is quotations (“”), which is the value that Excel returns if there are #N/A errors and makes the cell blank.
- As a consequence of the above, the IFNA function is “IFNA(VLOOKUP,””)” or, more precisely, “=IFNA(VLOOKUP($I$5,$B$5:$E$34,G6,FALSE),””)”.
I use substantially the same formula structure for the other cells in the results table. Excel now returns the following:
You’ll probably agree with me that this looks cleaner and more organized that having Excel display several #N/A errors. A similar thing happens when I input a city that isn’t included in the original data.
Just to test that the whole table is actually working properly in all cases, let’s see what happens when I ask Excel to look up the data for the second city in the table (Nevada).
Which, as shown by the screenshot below, matches with the original data.
The formula continues to work. Great!
Now, let’s go back to the discussion about the cases where using the IFNA function is more appropriate than using IFERROR and see how this applies to this specific case.
Why Using The IFNA Function Is More Appropriate Than Using IFERROR In The Example
As you’re probably aware, Excel allows you to trap and handle errors in a variety of ways. One of the most popular ways of doing this is using the IFERROR function.
There are good reasons for using the IFERROR function to trap errors, handle them and improve the user experience. However, is important to understand in which situations IFERROR is the best option and, in which cases, you are better off using another function or tool to achieve your desired objective.
In the example above, you could use the IFERROR function instead of the IFNA function. In such a case, the formula that returns the adult population of a city looks as follows: “=IFERROR(VLOOKUP($I$5,$B$5:$E$34,G6,FALSE),””)”.
The syntax is substantially the same as that of the IFNA function used previously. You just need to replace “IFNA” with “IFERROR”.
When looking for a city whose data is not included in the original table, both IFERROR and IFNA lead to the same result. Excel leaves the cells for adult population, size and population density blank.
However, for the reasons I explain below, the example above is one of the cases where using IFERROR is not the best option.
Why is the IFNA function more appropriate than IFERROR in this particular case?
In one of the sections above, I explained how the IFNA function is much more specific than the IFERROR function. IFNA traps only #N/A errors, whereas IFERROR traps any error.
In the example above, the type of error that we want to trap is that caused when we type the name of a city whose data doesn’t appear in the full table. This is a #N/A error. The IFNA function traps these errors.
Yes. The IFERROR function also traps #N/A errors, but it also traps other errors that you may not want to trap and handle in the same way.
Let me give you an example:
Assume that the person who gathered the information for you made a couple of typos when creating the original data table. As a consequence of these typos, the size of 2 cities appears (erroneously) as 0. Take a look at what happens in the data table.
Since the population density is calculated by dividing the adult population of a city by its size, Excel displays the #DIV/0! error for the cities whose size has been (erroneously) typed as 0 acres.
This looks like the kind of error that you wouldn’t want to handle in the same way as the #N/A error caused if you ask Excel to look up a city that isn’t part of the database…
But that’s exactly what IFERROR does. Take a look at the results obtained when asking Excel to get the data of the second city (Nevada) in the Excel worksheet that uses the IFERROR function:
Compare the results above with those obtained using the Excel worksheet with the IFNA function:
The difference between the 2 screenshots is quite evident. When using the IFNA function, Excel displays the prominent #DIV/0! error. If you’re using the IFERROR function, Excel leaves the cell blank and doesn’t give you evidence that there is a #DIV/0! error which you may want to take care of.
The data set that I’ve used for this example is relatively small and, due to the way that the Excel worksheets are designed, you may be able to notice the existence of a problem with the data without much problem. Additionally, you’d probably notice that a city with 1,042 adults can’t have a size of 0 acres or a non-existent population density.
But in real life you’ll probably be handling much more extensive and complicated data sets where noticing an error may be much more complicated. The question is, then:
Do you want to risk missing an error in your data just because you failed to choose the most appropriate function for a particular situation?
Off course not!
Fortunately, after reading this Excel tutorial you know how to use the IFNA function and why is more appropriate to use IFNA than IFERROR in some particular situations, such as the above.
So what is the more general take-away from this?
After asking whether the IFERROR function was bad, expert Dick Kusleika suggested 4 guidelines for error checking in formulas. For purposes of this Excel tutorial, its worth highlighting 3 of these guidelines and how they can be applied when using the IFNA function. I suggest you take into consideration the following criteria when choosing how to handle errors with functions such as IFERROR and IFNA:
- Criteria #1: Always choose the error-handling function that has a higher level of specificity. In other words, when choosing an error-handling function (such as IFERROR or IFNA), use the one with the smallest scope (the more specific one) that allows you to achieve your goals.
In the example that I used throughout this tutorial, the objective was to trap and handle the errors caused when asking Excel to look up a city whose data wasn’t available. IFNA allowed us to achieve this goal and it has a smaller scope than IFERROR. Therefore, the IFNA function was a more appropriate choice.
- Criteria #2: Wrap the smallest portion possible (of a formula) with the IFNA function.
- Criteria #3: Use IFNA only when you have a specific reason to do so.
In other words: don’t go overboard with the IFNA function. Just as using the IFERROR function indiscriminately may lead to problems, wrapping all of your functions with IFNA can also hide errors or mistakes that you may want to find.
Remember, for example, how I mentioned previously that Excel expert Jon Peltier only uses 2 error-trapping mechanisms. The good news is that one of the error-trappings he uses is substantially the same as that which I showed you in the example above: using the IFNA function to handle the errors caused by Excel not being able to find lookup values. So you already know one of the main uses of the IFNA function.
The IFERROR function is a very powerful and useful Excel function for those cases where you need a catch-all formula for several errors. However, this wide scope makes the use of IFERROR inappropriate in some cases.
You can use the IFNA function in those cases where you need to use a formula that has a smaller scope and is more specific than IFERROR. IFNA is a powerful alternative to IFERROR and can be extremely useful when you need to trap and handle #N/A errors.
One of the most common use-cases of the IFNA function is in conjunction with reference functions such as HLOOKUP, MATCH and VLOOKUP. Due to the importance of these cases, I have shown you an example of how to use the IFNA function to trap and handle the #N/A errors that can be generated when using the VLOOKUP function. Remember that you can get the Excel workbooks that I’ve used for this example for free by clicking here.
If you’ve read this whole Excel tutorial, you’re definitely ready to start using the IFNA function in Excel. Among other things, you know:
- What is the purpose of the IFNA function.
- When you should use IFNA instead of IFERROR and what other criteria you should consider when choosing which function to use.
- What is IFNA’s syntax.
- How to combine IFNA with reference functions (such as VLOOKUP).
Now that you know all of these, let me ask you:
Do you use the IFNA function? If you do, in which situations do you use it?
I am very interested in learning whether you use the IFNA function and, in case you do, in which situations do you use it. So please go ahead and let us know by leaving a comment below.
I would also be interested in hearing your opinion regarding the following topics:
- What do you think of the IFNA function? Was it a good addition to Excel?
- Do you, generally, prefer using the IFERROR function to the IFNA function?
- What criteria do you use to determine whether to use or not use IFNA?
Books Referenced In This Excel Tutorial
Click on any of the images below to purchase the book at Amazon now.