If you don’t know these 3 essential rules, it is likely that you will meet some problems when performing a VLOOKUP number in Excel…
- VLOOKUP function will show you a #N/A message when the lookup number does not have the same number of digits as the number in the left index column of the table array.
- Excel VLOOKUP will return #N/A errors when the number is stored as text in the left index column.
- The Excel VLOOKUP function will show you a #N/A message when the number has quotation marks, as you do with texts.
Golden Rule #1 is very important to do a VLOOKUP number effectively in Excel!
Stop the headaches of trying to realize why Excel is retrieving an error. For example, the formula: =VLOOKUP(13270.00,$A$2:$C$6,3,0)… returns #N/A.
You usually are aware that the lookup number 13270.00 is on the left index column.
The fact is, that the error message means the number is not there. You usually insist, yes the number is there because I am seeing it. Big mistake!
The backend left column shows the number 13270.003 as 13270.00. What you see is not what you get, so for Excel all digits count in a number match.
VLOOKUP number Golden Rule #2 – Never have numbers stored as texts in the index column…
Why does Excel store a number as text?
Here are some common reasons: a number with a leading apostrophe, a number with the wrong decimal delimiter, longer numbers after being imported may be turned into texts also.
I will show you an example…
This VLOOKUP: =VLOOKUP(5250,$A$2:$C$6,3,0)… retrieves the error #N/A
The reason is that the left index column has the number 5250 stored as text. You can be aware of this by seeing a green symbol to the left upper corner of the cell. Another good tip to detect numbers stored as texts is that texts are automatically aligned to the left.
So a number aligned mysteriously to the left is likely to be a text. Here is a handy tactic to convert the numbers stored as texts, to numbers, in mass.
- Select an empty cell
- Copy (basically a zero on the clipboard)
- Highlight the cells with the numbers that are stored as text
- Paste Special (CTRL+ALT+V)>Add
- Press OK
VLOOKUP number Golden Rule #3 – Never write the lookup number with quotation marks…
For example, the formula, =VLOOKUP(“52503,$A$2:$C$6,3,0)… retrieves the message #N/A. I am assuming here that your left index column has numbers (5250 for this case). I refer to this because the formula: =VLOOKUP(“52503,$A$2:$C$6,3,0) will not retrieve #N/A if the left column contains the number 5250 stored as text.
The fact that these small issues force you to debug and have doubts about the function results, means that you will get more productivity once you apply these rules…
You will speed up the VLOOKUP formula writing stage and increase your confidence in the results. This, as I said, will increase your productivity in the end, and it is also my wish that it will more rapidly advance your career and your overall value in your job and profession. Please always enjoy working with your data, and let me know how you are doing. I am here to help, and always glad to do so in any way that I can.