Vlook up and Zipcodes

**ChefBacon** · 03-09-2009, 10:16 AM

Hello all,

I'm creating a look up function for the staff where you enter in the zip code, and it returns a series of answers (the state the zip code is attached to, the shipping code to use for regular shipping, next day air, and IF it is available for that zip code, the codes to use for Saturday shipping).

My problem is the fact that I'm looking up zipcodes. I have the cells, both the one where you enter in the zipcode to look up AND the entire column of the 29,000+ zipcodes formatted as "zipcode". However the cell where you enter in the zipcode.. it shows the "0" in the cell, but Excel thinks it's four numbers.. it's not recognizing the "0" in the function line. This means Vlookup is trying to find a 4 digit number, and not the 5 digit one I am asking it to, thus I get #N/A as a result for any zip code starting with "0". Every zipcode that starts with 1 or higher looks up just fine.

I have it set as FALSE so it will return the #N/A when you enter in a nonshippable zipcode, or one that doesn't exist.

If I change to TEXT, then it will not look up anything past "0" in the list, so all answers higher that 070xx (new jersey) come back as #N/A

I'd really like to have just 1 look up, not two, one for Zipcodes starting in "0" and one for zipcodes starting with "1-9". Does anyone know what I can do here? I've tried combinations, but am I just missing something? I'd rather not go back and just drop the 0 in the lookup column of the zipcodes to make this work.. I spent hours reformatting this whole column to show right int he first place.

Thanks for any help you can offer me.

**GuruWannaB** · 03-09-2009, 10:37 AM

I am assuming you have a field that is the input for the lookup....format this field as a zipcode format as well...then have your vlookups based on that and all should be fine.

UPDATE: I've added an example spreadsheet

**ChefBacon** · 03-09-2009, 10:59 AM

Yes the field for the lookup has been tested as both zipcode AND text. Zipcode will not look up any of the "0" zipcodes... Text will not lookup anything past the "0" zipcodes.

**ChefBacon** · 03-09-2009, 11:01 AM

I see, your example is seeing the 0 zipcodes without the 0's in both the lookup and the input field. For some reason, mine isn't doing this.

**GuruWannaB** · 03-09-2009, 11:09 AM

Are you able to post a file?

**ChefBacon** · 03-09-2009, 11:37 AM

Ah ha!

I realized that in formatting the 29,000+ list, all of the 0 zip codes were formatted as text. I reformatted as number, then changed to zip code.. and now it works.

Thanks for the help, your example actually helped me figure that one out. Now it works perfectly.

Thanks again!