Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to vlookup numbers stored as text in Excel?

doc vlookup number stored as text 1

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?

Vlookup numbers stored as text with formulas


arrow blue right bubble Vlookup numbers stored as text with formulas


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:

doc vlookup number stored as text 2

Notes:

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.

doc vlookup number stored as text 3

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

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Rens · 1 years ago
    Unfortunately 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.