Vlookup retaining leading zeros

[olivergs1982]

I have a pretty log winded spreadsheet that's over complicated but other than this vlookup, it works so please, any assistance would be greatly appreciated.

My formula is =VLOOKUP(A4,Names!$A$9:$F$300,5)
A4 contains number 1

My list "Names" looks something like this [TABLE="width: 315"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10449[/TD]
[TD]
Blogs, Joe[/TD]
[TD]0004C[/TD]
[TD]0004[/TD]
[/TR]
</tbody>[/TABLE]

The column index number result is 0013 and I need the vlookup to retain the preceding zero's as this then is then referenced elsewhere in part of a sumif where the data comes from a bespoke system that index's the number as 0013 and I can't get rid of the preceding zero's here so I need the be able to get my vlookup to give a result with the preceding zero's that another formula can reference. Preferably without having to use VBA.

Thanks.

 

[gaz_chops]

Not sure I follow, is it the lookup value that has the leading zero's or the lookup result?

If it is the result, try

=TEXT(VLOOKUP(A4,Names!$A$9:$F$300,5),"0000")

 

[Joe4]

VLOOKUP will return the EXACT value, not the formatted display.
If you see that VLOOKUP is returning a value and dropping the leading zeroes, it almost certainly means that the leading zeroes really aren't there. What you have is a number that has a Custom cell format on it, will shows leading zeroes (even though they really aren't there). An easy way to confirm that is to go to the cell that has the matching value to return, and temporarily change the format to "General", and see what it looks like.

The only way to enter a number and to retain leading zeroes is to enter it as Text, not Numbers (as leading zeroes have no meaning to numbers).

To emulate the Custom formatting a number may have, you can use a solution like gaz_chops provided, using the TEXT function to tell it to return a zero padded 4 digit number.
Or, you could just apply the same Custom format to the cell with the VLOOKUP formula that the original cell it is getting the value from has.

In a nutshell, VLOOKUP only returns values, not formatting.