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”. 🙂
wmfexcel 
I noticed long ago that Vlookup function is pretty fussy.
I usually work with large tables of information, so inserting an ‘ apostrophe into each cell is not an option for me and format cell as text sometimes does not help. The only option which works for me is to go all the way to Data tab and use Text to Columns (select whole column) at the very end “Column data format” change “General” to “Text”.
LikeLike
You won’t need to do that now.
Merry Christmas in advance! 🙂
LikeLike
Merry Christmas to you MF as well (and to all readers)!
In my “table_array” “lookup_value” stored as text so “value(lookup_value)” doesn’t work, but “trim(lookup_value)” does the work perfectly. Thank you so much for good advise.
LikeLike
How do you do a vlookup which looksup data irrespective of text or values.
I believe somewhere I read you use
-(dash) in between the formulae
LikeLike
HI Ali,
Vlookup considers Text and Value are two different things. Depends on your lookup value and the value in the lookup table, you need to “convert” the lookup value to fit the lookup table.
You may use TRIM to convert a value to text, or VALUE to convert a number stored as text to value.
You may refer to the last 3 paragraphs of this post.
Cheers,
LikeLike
Hi MF,
What if I have lookup value have both text and value also source data have both text and value.
LikeLike
If that’s the case, you should clean your data first.
Try Power Query or Get and Transform in Excel 2016.
LikeLike
Pingback: Vlookup number vs. text
Pingback: Odd VLookup Results - Page 3
Pingback: Alternative to vlookup – Index and Match | wmfexcel
Pingback: vlookup – True or False?? | wmfexcel
Pingback: vlookup with Match | wmfexcel
Pingback: Tips in constructing vlookup | wmfexcel
Pingback: The basic of vlookup | wmfexcel