Why does vlookup not return the lookup value? I am sure it is in the source data table. I see it by my eyes.
Have you ever experienced this?? That’s normal when you import your data from other database. Fortunately it can be solved with a little trick.
Apparently, vlookup considers text string and number two different things. Therefore “1” (as text) is different from 1 (as number). Although we have “1” in the data table, the lookup_value we input is 1 (number). In this way, Excel returns “#N/A” to mean you that the lookup_value is not available in the table_array.
In the source data table, the “Rank” is actually “Text”. Note the little “Green Triangle” in the upper left corner of the cell. Click on it, then you see the Smart Tag (the exclamation mark). Click on the Smart tag and you see the message; and options.
If you convert the “Text” to “Number” from the source data table, you will have the correct answer as shown below:
However, I do not recommend you to convert the “text” to “number” in the source data. Indeed, you should avoid changing anything in the data source unless you have a valid reason to do so.
It is easier to convert the lookup_value into “text”. We may input with a leading ‘ (apostrophe coma) that tells Excel you are about to input “text” instead of “number”.
Another approach (recommended) is to do a simple trick in the formula as follow:
Trim is a text function. By incorporating trim(lookup_value) in the formula, Excel will convert the lookup_value into “text”. In this way, we are looking up “text” in a table_array with “text”.
In case the other way round, i.e. “number” in the source data while “text” as the lookup_value, we just need to convert the vlookup_value from “number” to “text”. There are many ways to do so, e.g. by *1, by +0 (both basic maths give you exactly the same number as it was); or you may use value(lookup_value) to convert “text” to “number”.
Not difficult, right? Just that you have to be clear about which one is “text” and which one is “number”. 🙂
Other topics about vlookup:
- The basic of vlookup
- Tips in constructing vlookup
- vlookup with Match
- vlookup options – True or False?
- Advanced vlookup – Text vs. Number
- Advanced vlookup – Wildcard Characters “?” and “*”
- Alternative to vlookup – Index and Match
- Three different ways to do case-sensitive lookup