Reuse: Quickly insert complex formulas, charts and anything that you have used before Encrypt Cells with password Create Mailing List and send emails.Kutools for Excel - The Best Office Productivity Tool Increase Your Productivity by80% 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.
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.ģ. 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.Ģ. 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:Įnter 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:ġ. 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Īmazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari! Save 50% of your time, and reduce thousands of mouse clicks for you every day! 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. Last week a co-worker needed to compare values on a report from two different dates.
And I keeping finding more and more ways and reasons to use it. I recently discovered the value of vlookup in excel. Such as 123.45 is not equal to 10001 that see as only 2 decimal place as 123.45. If your data is number, make sure that the number you see is equal to its value especial the digits. VLookup N/A is not due to cell format only as Text format but it also depends on the value and type of your data. This formula looks at the value in cell E2 (a B), considers the range of cells A2:B6, finds the matching row containing the value B, and returns the second column’s value in cell B3: 80. The first pair is very easy to do with VLOOKUP where the formula in cell F2 would be: =VLOOKUP(E2,A2:B6,2,FALSE).
How to vlookup numbers stored as text in Excel?