#N/A in Excel: what it means and how to fix it
#N/A means 'not available': a lookup function such as VLOOKUP can't find the value.
Try the first exercises for freeIn the lesson Looking up with VLOOKUP
What does #N/A mean?
#N/A means not available: a lookup function such as VLOOKUP, XLOOKUP or MATCH can't find the value you're looking for.
Sometimes that's right, because the code really isn't in the list. Often the value does seem to be there, but is slightly different: a hidden space, a number stored as text, or a table that has shifted because it wasn't locked.
When do you get #N/A?
- The code you're looking for isn't in the first column of your table.
- There's a space after the value in the cell or in your table.
- One cell contains the number 1042, the other the text 1042.
- You filled VLOOKUP down without locking the table with dollar signs.
How to fix #N/A, step by step
This is how to fix the error:
- Check that the value really is in the first column of your table. Look for it with Ctrl + F.
- Compare the length of the two cells with LEN. If one is longer than it looks, there's a hidden space: remove it with TRIM.
- See whether one is a number and the other text: a number stored as text is on the left of the cell. Convert it with VALUE.
- If you filled the formula down, lock the table with dollar signs, such as $A$2:$C$50.
- If it's normal for a code to be missing sometimes, catch it with IFNA, so that you see a neat message.
Examples
The error
E1 contains =VLOOKUP("P-999",A2:C5,3,FALSE). The code P-999 isn't in the list.
A hidden space
E2 contains =VLOOKUP(D2,A2:B2,2,FALSE). In A2 there's a space after the code. To Excel, P-101 with a space is something different from P-101 without one.
A neat message
E1 contains =IFNA(VLOOKUP("P-999",A2:C5,3,FALSE),"Unknown"). IFNA shows your own message, and only if the code is missing.
Sometimes just leave #N/A
An #N/A tells you a value is missing. So don't just hide the error. Only catch it once you know it's normal for some codes to be missing, for example with IFNA.
Where it often goes wrong
- A number stored as text looks identical, but isn't found.
Practise this in the lesson Looking up with VLOOKUP. The first exercises are free.