vlookup not returning leading 0

[daveyc18]

i have a vlookup against another tab and the numbers that have ONE leading 0....theyre stored as text....when i do the vlookup, tho, the leading zero isn't returned..

is there a way to pick it up without having to concatenate?

 

[jtakw]

Hi,

If the value(s) in the Lookup Table is Truly Text with leading 0s (zero)s, VLOOKUP Will return them intact (with the leading 0s), so may be the leading 0s are Not actually there? May be the cells are custom formatted to show leading zeros?

 

[daveyc18]

Hi,

If the value(s) in the Lookup Table is Truly Text with leading 0s (zero)s, VLOOKUP Will return them intact (with the leading 0s), so may be the leading 0s are Not actually there? May be the cells are custom formatted to show leading zeros?

they are text....excel says "number stored as text"

 

[Joe4]

Please post your exact VLOOKUP formula.

If you change the format of the cells of the original range you are pulling back from to "General", what do those values look like? Do they still have leading zeroes?
If not, they are actually numbers with Custom Formatting. VLOOKUP only returns the actual underlying value, not the formatting of the cell.

 

[Aladin Akyurek]

Try to post the result of the following:

=A2&""

where A2 houses a datum of interest.