Vlookup Issue With Leading Zeros

[JADownie]

I am having an issue with a vlookup today, and wanted to see if I could get some opinions or tips to solve this.

I have a list of site codes which contain numbers with two leading zeros. Then on the next tab I have a form created where users type in the site code, and in the adjacent cell the name of the site will appear (I have the vlookup in this cell referencing the user input value to the left). However when I input a site code (ex. 00123) in the cell it only stores the value 123. I tried doing a custom number format ("00"#) and that displays the 00123 in my actual cell, but the vlookup still returns an error because it is only looking up 123.

Is there anyway to work around this or is there something I am doing wrong here? Thanks So much in advance because this is driving me crazy today!

 

[Special-K99]

Try concatenating two zeroes to the VLOOKUP value

=VLOOKUP("00"&A1...)

 

[JADownie]

Thanks that just worked great - I did not even think to do that!

But now I have another related issue. In my list of 100 codes, I just saw that 90 have two leading zeros and 10 have one leading zero. I am assuming that there is no way to account for this in my vlookup?

My formula is starting to get long...

=IFERROR(IF(D48="","",IF($K$12="MB",VLOOKUP("00"&U48,'Sigma vs Vendor'!A:I,8,FALSE),IF($K$12="TNN",VLOOKUP(U48,'Sigma vs Vendor'!B:J,9,FALSE),IF($K$12="DL",VLOOKUP(U48,'Sigma vs Vendor'!A:I,8,FALSE),"")))),"Check Site Code")

 

[Person93]

Perhaps you can add a column that looks that the codes and reformats it without the leading zeroes.

Then, you can have the vlookup use that column.