how to do a vlookup on a source that has numbers that look like text?

[bigdan]

My source file has numbers in Col A but look like text or are text. You know how you sometimes have numbers with those tiny triangles in the top left which mention it's stored as text? That stuff.

How would I do a vlookup on that? I'm trying to do one right now and this source file does have the stuff I'm looking up but I'm getting no match. This would be the reason I assume.

With my result data I tried formatting the numbers I'm looking up as text thinking that might work; so that it would be text doing a vlookup on text. That didn't work either.

 

[jasonb75]

Try changing the formula to something like

=VLOOKUP(A1&"",lookup table,2,0)

 

[smalik]

I was just going to ask the same question. I am having a similar issue. My issue is I am downloading data from two different applications. One application lets me export the data into xlsx format while the other application only lets me download as pdf. I then convert pdf into Excel. When converting the data from .pdf to .xlsx I lose the ability to convert the formatting to general, it stays as text. Hence when I do a vlookup or even xlookup I do not get the desired results.

Any help from the experts is greatly appreciated.

 

[smalik]

Try changing the formula to something like

=VLOOKUP(A1&"",lookup table,2,0)

Do I need to convert the data into a table format?
What is lookup table ,2,0 mean?

Thanks.

 

[smalik]

Do I need to convert the data into a table format?
What is lookup table ,2,0 mean?

Thanks.

Actually, never mind silly questions. What was I thinking....?

 

[EXCEL MAX]

Maybe this

=IF(ISNUMBER(A1);A1;TEXT(A1;"0"))

 

[EXCEL MAX]

Actually,
=VLOOKUP(IF(ISNUMBER(A1);A1;TEXT(A1;"0"));lookup table;1)

 

[jasonb75]

Actually,

That wouldn't work if A1 is decimal. Joining double quotes to the cell value works with any integer or decimal value without the need for 3 additional functions that you have used.

Neither suggestion (yours or mine) will work if the value in A1 is currency, date, percentage, etc.

 

[bigdan]

Try changing the formula to something like
Excel Formula:
=VLOOKUP(A1&"",lookup table,2,0)

that worked! thanks so much! can you explain what the quotes are doing?

 

[jasonb75]

Converting the content of A1 to text.

Text and blanks remain unchanged, numbers are converted to text using the actual value.

The point to remember is that it will not work with numbers that have any kind of special formatting, for example, date, time, currency, percentage, comma separated thousands or decimals if they are truncated by formatting.