If you don’t know these 3 essential rules, you are likely to run into some problems when performing a VLOOKUP number in Excel …
- The 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 index column to the left of the table array.
- Excel VLOOKUP will return # N / A errors when the number is stored as text in the left index column.
- Excel’s VLOOKUP function will show you a # N / A message when the number has quotes, just like it does text.
The golden rule no. 1 is very important to do VLOOKUP number effectively in Excel!
Stop the headaches of trying to figure out why Excel is recovering from an error. For example, the formula: = VLOOKUP (13270.00, $ A $ 2: $ C $ 6,3,0) … returns # N / A.
Usually you know that the search number 13270.00 is in the index column on the left.
The fact is that the error message means that the number is not there. You tend to insist, yes, the number is there because I am seeing it. Big mistake!
The left column of the backend 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 n. # 2: never save numbers 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 can also be converted to text.
I’ll show you an example …
This VLOOKUP: = VLOOKUP (5250, $ A $ 2: $ C $ 6,3,0) … retrieves the # N / A error
The reason is that the index column on the left has the number 5250 stored as text. You can tell this by seeing a green symbol in the upper left corner of the cell. Another good tip for detecting numbers stored as texts is that the texts are automatically left aligned.
So a number mysteriously left-aligned is likely text. Here’s a useful tactic for converting numbers stored as text, to numbers, in bulk.
- Select an empty cell
- Copy (basically a zero to the clipboard)
- Highlight cells with numbers that are stored as text.
- Paste Special (CTRL + ALT + V)> Add
- press OK
VLOOKUP number Golden rule n. 3: never enclose the search number in quotes …
For example, the formula, = VLOOKUP (“52503, $ A $ 2: $ C $ 6,3,0) … retrieves the message # N / A. I assume here that the index column on the left has numbers ( 5250 for this case.) I mean 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 problems force you to debug and have doubts about the results of the function, means that you will get more productivity once you apply these rules …
It will speed up the writing stage of the VLOOKUP formula 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 you further 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’m here to help, and I’m always happy to do it in any way that I can.