# Vlookup Issue With Leading Zeros

##### Active Member
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

##### Well-known Member
Try concatenating two zeroes to the VLOOKUP value

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

##### Active Member
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

##### New Member
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.

1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...