vlookup with zip codes - not working - frustrated

[chrissyb]

Hi... I've wasted a full day today trying to do vlookup with zipcodes. Some
zipcodes start with zeros. I want to import county from one worksheet with
zip codes into another worksheet with zipcodes containing other data. But I
can't trim and clean my data like I do with account numbers because I lose
the leading zeros on some zip codes. And for some strange reason I can't get
the zipcodes on both worksheets into the same format. If I make them both
zip code format it won't work. If I make them both text, some but not all of
the leading zeros are lost. I can't get them into identical format for
vlookup. I'm at a loss here with this usually simple vlookup task that has
been a bear today. PLEASE advise.

 

[R1C1]

I tested VLOOKUP for a cell and range table formatted as zipcode and text,
one of which would be required to properly display the leading zero. Both
lookup without error as long as they are both formatted the same, either
zipcode or text.

You said, "for some strange reason I can't get the zipcodes on both
worksheets into the same format", this may be due to the fact that just
because you format the cell doesn't mean what you see in the cell is
formatted accordingly. If text or a number format exists and you format it
zipcode, the date in the cell is not actually formatted to zipcode until you
activate the cell and press enter (this can be done programatically also).
If a cell contains a zipcode fomat and it is formatted text or number
format, the data will not be text or a number until you activate the cell
and press enter (this can be done programatically also). I would venture to
guess that this is the reason for you saying you can't get them fomatted the
same.

If this doesn't help, post your lookup fomula for varification.


Regards,

Alan

 

[Max]

Perhaps try padding leading zeros to the lookup values to match that within
the source table's 1st col (the lookup col), something like this in B1:
=VLOOKUP(TEXT(A1,"00000"),ZipTable1,2,0)

 

[bony_tony]

Try selecting the column with the zip codes, then hit Data--->Text to
Columns--->Next--->Next--->Select "General"---> Then finish
This does the same thing as R1C1's comment about activating the cell
and pressing enter.

Cheers
Tony