Sometimes, when you’ve used a bunch of lookup tables on your Excel sheet, you might spot a #N/A error. The #N/A error is typically associated with lookup tables, but it can also appear when you’ve missed inserting an important element in your formula. If you’ve got some #N/A errors on your worksheet, we’re going to help you get rid of them in the next couple of minutes.
What is a #N/A Error in Excel?
The #N/A error in Excel is Excel’s way of telling you that you’ve either entered something that Excel can’t identify or find, or that you’ve misspelled something in your formula. This makes #N/A errors inherently common among lookup functions.
Why Does it Appear?
There can be two reasons for a #N/A error.
The first reason is that you’ve entered information that Excel is unable to find or locate.
The second reason is that you’ve misspelled some information causing Excel to look for incorrect information on your worksheet.
#N/A errors occur most commonly, though not always, with lookup functions. If that’s true for your case, here’s what you can do about it:
- Verify the accuracy of the lookup value and lookup table. Make sure everything is spelled correctly with no unnecessary blank characters.
- Ensure that the lookup table has all values required by the lookup function.
- Verify if the lookup range entered in the function is complete.
- Check if the type of lookup value and lookup table match. Both should either be text strings, numeric values, etc.
However, if you want to quickly clean your worksheet, you can trap the #N/A error. Let’s walk through a few examples and see how it works on a spreadsheet.
Examples
You can use two functions to trap the #N/A error—IFERROR and IFNA.
1. Fixing the #N/A Error Using the IFERROR Function
Say you had a top-notch game last week. In the end, everybody promised not to keep scores. It’s just a game right.
You come home, you secretly pull up your spreadsheet, and start punching in the game’s score. It’s okay to make yourself feel good, but it turns out someone accidentally deleted your friend Ricky’s score from your spreadsheet.
You pull up your archives and run a VLOOKUP to see if you can find Ricky’s scores again. But your spreadsheet returns a #N/A error.
Oh, well, no big deal. You can always beat Ricky again tomorrow. But what about that #N/A error though? If you’re someone who doesn’t like seeing errors on the spreadsheets, that’s going to cause you grief.
Let’s look at what you can do to remedy this.
Here’s the VLOOKUP formula you used:
=VLOOKUP(D5,$A$2:$B$6,2,0)You verify the formula, and everything looks good. You verify the lookup value in the referenced cell and everything looks good. Then, you verify and realize, since Ricky’s entry has been deleted, the VLOOKUP function can’t find that value in the lookup table.
Now, you can always add Ricky’s name and score, or remove the VLOOKUP formula. But what if you want to add Ricky’s name later, and still not see the #N/A error. Well, you’ll need to trap the error using the IFERROR function, like so:
=IFERROR(VLOOKUP(D5,$A$2:$B$6,2,0),"Player Not Found")
The IFERROR essentially returns the value in the second argument every time that the first argument’s return is an error. Note that this formula will trap any error. Easy peasy, eh?
But there’s one more way to go about this.
2. Fixing the #N/A Error Using the IFNA Function
What happens when you misspell a function’s name? It returns the #NAME? error. However, if you use the IFERROR function as a blanket solution, you probably won’t realize this, because as mentioned, the IFERROR function traps all errors.
Fortunately, there’s an IFNA function. The IFNA function specifically targets #N/A errors. If your formula returns any other error, you’ll see the error and not the substitute value.
So, say you’ve applied the IFERROR function to all entries in your VLOOKUP output, just in case there’s a #N/A error. What then happens is, you’ll also see the "Player Not Found" output for players that are actually on the list.
For instance, say you’ve misspelled the formula in one of the cells. Technically, you should see the #NAME? error. However, when you apply the IFERROR function, you’ll see the output as “Player Not Found.”
Instead, you could use the IFNA function, like so:
=IFNA(VLOOKUP(D5,$A$2:$B$6,2,0),"Player Not Found")
Notice how applying the IFNA function retains all errors except the #N/A error. In our example, Mitchell’s score does exist in the table. However, IFERROR directly said the player’s not in the list because the formula returns a #NAME? error—in this case because of a misspelled formula. The output for Ricky, though, remains the same with the IFNA function because it screens out all #N/A errors.
3. Force #N/A Error
So far, we’ve focused on getting rid of the #N/A error. But did you know that Excel has an NA function that you can use for forcing the #N/A error whenever a value is missing on your spreadsheet?
Ideally, the function is most useful with conditional formulas like the IF function and SUMIF function. For instance, if you want to return a #N/A error whenever a certain cell’s value is negative, you can use the following formula:
=IF(A3<0,NA(),A3)
This way, the cell will always display a positive value from the cell that’s referenced in the formula. If the referenced cell has a negative value, you’ll see a #N/A error.
That’s all you need to know to get rid of literally all #N/A errors on your worksheet (or even add a few #N/A errors). While you get up to speed with these solutions, we’ll go load up another tutorial for you to get busy with. Stay curious.
About Supriya