How to vlookup numbers stored as text in Excel?
Supposing, I have the following data range, the ID number in the original table is number format, in the lookup cell which is stored as text, when I apply the normal VLOOKUP function, I will get an error result as below screenshot shown. In this case, how could I get the correct information if the lookup number and original number in table have the different data format?
If your lookup number is stored as text, and the original number in the table is real number format, please apply the following formula to return the correct result:
Enter this formula: =VLOOKUP(VALUE(G1),A2:D15,2,FALSE) into a blank cell where you want to locate the result, and press Enter key to return the corresponding information you need, see screenshot:
1. In the above formula: G1 is the criteria that you want to lookup, A2:D15 is the table range which contains the data that you want to use, and the number 2 indicates the column number which has the corresponding value you want to return.
2. If your lookup value is number format, and the ID number in the original table is stored as text, the above formula will not work, you should apply this formula: =VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE) to get the correct result as you need.
3. If you are not sure when you'll have numbers and when you'll have text, you can use this formula: =IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0)) to handle both cases.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 years agoUnfortunately this method does not Always work. I import numbers in a column defined as tekst. I lookup these numbers in a table in which the numbers are also stored as tekst. I would say a Vlookup should work, but nor the standard lookup, nor the lookup with TEXT() or VALUE() function as expected. When I retype all values in the columns it works as expected, so there's no error in the formula.