MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula Help


Posted by Chad on December 02, 2001 1:44 PM

I need to enter the first three digits of a zip code and have excel give me its assigned zone code.
Example:
Zip Code Zone
117-126 7
127 6
128-129 7
130-132 6
133-136 7

There are 7 different zone codes (2-8), and about 150 different zip code categories.
Thanks in advance for any help.


Posted by Bariloche on December 02, 2001 5:52 PM

Chad,

Probably the easiest way is to use VLookUp (click on the f(x) button and select "LookUp & Reference" from the Function Category list).

First create a table of the 3 digit ZIP and Zone codes, like:

3DigitZip ZoneCode
117 7
118 7
119 7
120 7
121 7
122 7
123 7
124 7
125 7
126 7
127 6
128 7
129 7
130 6
131 6
132 6
133 7
134 7
135 7
136 7


I put that table on Sheet1, then I inserted a Sheet2 and in cell B2 I entered: =VLOOKUP(A1,Sheet1!A$1:B$21,2,FALSE)

Now, when a 3 digit ZIP is entered in Sheet2, cell A1, Sheet2, cell B2 will display the Zone Code.

Modify the above to suit your particulars. You can find more info on VLookUp in Excel Help.

enjoy