Learn how to fix cells that show #NA after calculation with this short guide that explains why the error occurs and how to prevent the error from displaying those ugly #NA values all over your spreadsheet…
Why does the #NA error occur?
The #NA error occurs because a look up value cannot be found in your lookup data range. Excel attempts to lookup a value, cannot find it in the cells you have told it to look so it returns the #NA result. This is good in a way as it tells us something is wrong and that we have something to fix in the Excel cells.
Take the following example where there are a list of employees and their tax codes, we have a lookup table of tax codes and rates and the challenge is to match the corresponding tax rates to the employee based on their tax code:
In the example we have a list of employees and their tax codes in columns A and B and a lookup table of tax codes and the corresponding rates in columns F and G. The VLOOKUP function is used to match the tax code in column B to the one in Column F and return the tax rate in column G.
This works fine until cell D8, highlighted in the image, it has returned the #NA value and this tells us the expected value cannot be found in our lookup table. There are generally only 2 reasons for this:
- The range of the lookup table has not been specified correctly in the VLOOKUP statement.
- The value you are trying to look up, in this example the tax code, does not exist in the lookup table.
A check of the VLOOKUP statement shows we are looking up the range F4 to G8 for our tax codes and rates. That corresponds to our lookup table dataset in F4 to G8 so no problem there.
The tax code that is being looked up is in cell B8, the tax code “433-L” and we can look in column F to see if this exists. No, it is not on our lookup table list, so that is the reason we are getting the #NA error.
How to fix the cell or cells with the error
In Excel there is a way to fix your cells that have the potential for a #NA error. Obviously one way is to ensure all values that are looked up exist but sometimes that is not always practical as you may have huge lookup lists that are constantly changing. The solution therefore is to display something else rather than #NA, a statement such as “Not found in lookup” is more useful than a blank as it tells us the formula is working but can’t find something, but equally you can return a zero or anything you like as a default value.
The fix is achieved by introducing a couple of extra statements to your formula. The first of these is the ISNA function, which takes the format:
The ISNA function works by testing the result of a formula, or value of a cell, against whether the outcome is #NA or not. If it is #NA then then result is TRUE if not then it is FALSE. See the following examples below:
That’s the first piece of the puzzle, we can now tell Excel to check whether or not our result is #NA, so the next step is to include that within an IF statement, that way we can tell Excel what to do if the result is #NA, and what to do when it is not.
In plain English terms we want to say:
Result = IF FORMULA RESULT = #NA THEN CHANGE TO “something”, ELSE SHOW FORMULA RESULT
If we go back to our original example we can say we would like to change the tax rate to show “Tax code Not Found” when it doesn’t match a tax code against our lookup table.
In order to do this we must take the original VLOOKUP statement which looked like:
And change it to:
=IF(ISNA(VLOOKUP(B4,$F$4:$G$8,2,FALSE)),”Tax Code Not Found”,VLOOKUP(B4,$F$4:$G$8,2,FALSE))
That looks quite a beast of a formula so let me break down the parts:
Implemented into the original example and the result is achieved, no more #NA error value:
One thing to watch out for is when applying this technique to long complicated statements, such as lots of nested IF statements and Lookups. As you can see the formula for fixing the Excel cells uses the original formula twice, the first time to test for the #NA error, the second time to calculate the result if a #NA error is not found. This can lead to very large formula statements which are memory intensive when used against a big dataset.
However, in the majority of scenarios you will come across in Excel this is a great trick to fix Excel cells that can potentially generate the #NA error value and help you do something more productive with them.