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?

Vlookup numbers stored as text with formulas

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:

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.

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.

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Read More... Free Download...

---

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download...