Vlookup Issue With Leading Zeros

JADownie

Active Member
Joined
Dec 11, 2007
Messages
358
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!
 

JADownie

Active Member
Joined
Dec 11, 2007
Messages
358
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
Joined
Jul 23, 2013
Messages
44
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top