vlookup woes

[mrxwanttobe]

Good Morning

So I have two sheets. both have Zip codes but on one sheet some of them are 00000,some are 00000000 and some are 00000-000. the other sheet are all 5 position 00000. for them to match I'm using =LEFT(A1,5) and it shortens them all to 5 positions but then Vlookup no longer works like maybe it's changing the format when I shorten to 5 positions. Any help would really be appreciated.

 

[Aryatect]

Hi,

Try below:

=VALUE(LEFT(A1,5))

 

[Fluff]

Left returns a string rather than a number, so you can use LEFT(A1,5)*1 to turn it back into a number

 

[mrxwanttobe]

So I tried both the =VALE and the Left with the *1 and both still drop the leading zero. Any more thoughts?

 

[Fluff]

If your values have a leading 0 then they are either text, or they are numbers that are formatted to show leading 0.
On the lookup table put this in a blank cell =istext(A1) where A1 is a cell in the 1st column of your lookup table.

 

[mrxwanttobe]

This returns the value of TRUE

 

[Aryatect]

Did this come TRUE from Sheet1 (with 00000-0000) or Sheet2 (with 00000) or both? please also check if there are any space character present.

 

[Fluff]

In that case they are text, not numbers, so there shouldn't be a problem.
What is you vlookup formula?

 

[mrxwanttobe]

Looks like I got it. Have to format the existing columns to Number/custom 00000. the I could get the new column to not drop the leading zero. thank you so much

 

[Fluff]

Glad you sorted it & thanks for the feedback