Excel USPS Zip Code Chart

[dfelmey]

Hello. I have a zip code chart from the USPS that is able to be in Excel format. Domestic Zone Chart - We are sending from the Zip with the first 3 of 173. The chart below is what the report looks like.

I need to have a cell that if we put the first 3 of a zip code (Zip3), it gives the corresponding Zone#.

Example: When zip code 014 is entered in Cell D1, F1 should say say zone 4, since it falls in the range of 013--045 from the chart below (highlighted in red for reference).

I don't know how to have the reference pull when the zip3 falls in the range of example: 013---045

Thank you in advance for any assistance.

ZIP Code	Zone	ZIP Code	Zone	ZIP Code	Zone	ZIP Code	Zone
005	3	255	3	537---551	5	769	7
006---009	7	256---257	4	553---561	5	770	6
010---012	3	258---266	3	562	6	772---779	6
013---045	4	267---268	2	563---564	5	780	7
046---047	5	270---275	4	565---567	6	781	6
048---051	4	276---279	3	570---577	6	782---785	7
052	3	280---301	4	580---587	6	786---787	6
053---059	4	302	5	588	7	788	7
060---077	3	303---309	4	590---596	7	789	6
078---079	2	310	5	597---599	8	790	7
080---084	2*	311	4	600	4	791---792	6
085---086	2	312---313	5	601	5	793---794	7
087	3	314	4	602---604	4	795---796	6
088---089	2	315---329	5	605	5	797---806	7
090---099	3+	330	6	606---609	4	807---808	6
100---127	3	331---342	5	610---617	5	809---816	7
128---129	4	344	5	618---619	4	820---831	7
130---135	3	346---347	5	620	5	832---833	8
136	4	349---352	5	622---623	5	834	7
137---153	3	354---369	5	624	4	835---838	8
154---159	2	370---374	4	625---631	5	840	7
160---161	3	375	5	633---641	5	841---844	8
162	2	376---379	4	644---658	5	845	7
163---165	3	380---384	5	660---662	5	846---847	8
166	2	385	4	664---665	6	850---853	8
167	3	386---398	5	666---667	5	855---857	8
168	2	399---418	4	668---680	6	859---860	8
169	2*	420	5	681	5	863---864	8
170---174	1*	421---427	4	683---693	6	865	7
175	2*	430---436	4	700	6	870---871	7
176	1*	437---447	3	701	5	873---885	7
177---199	2*	448	4	703	6	889---891	8
200---210	2	449	3	704	5	893---895	8
211	1	450---456	4	705---708	6	897---898	8
212	2	457	3	710---711	6	900---908	8
214---216	2	458---497	4	712	5	910---928	8
217	1	498---510	5	713---714	6	930---961	8
218---229	2	511---512	6	716---717	5	962---966	8+
230---241	3	513---516	5	718	6	967---968	8
242---243	4	520---528	5	719---729	5	969	9+
244---245	3	530---531	5	730---731	6	970---986	8
246	4	532	4	733---741	6	988---999	8
247---253	3	534	4	743	5
254	2*	535	5	744---768	6

[+] 5-Digit Exceptions​

ZIP Code	Zone	Specific To
09000---09999	4	Priority Mail service
09000---09999	4	USPS Ground Advantage (< 16 oz.)
96200---96699	4	Priority Mail service
96200---96699	4	USPS Ground Advantage (< 16 oz.)
96900---96938	8
96945---96959	8
96961---96969	8
96971---96999	8

 

[Scott Huish]

Ideally, I would get rid of the 2nd number, put it all in one column (sorted numerically) and do a VLOOKUP I have column A and cell B2 formatted as 000. (Not quite showing right in the sample, but it is in my sheet)

Book1
	A	B	C
1			Zone
2	First 3 Digits:	007	7
3
4
5	ZIP Code	Zone
6	5	003
7	6	007
8	10	003
9	13	004
10	46	005
11	48	004
12	52	003
13	53	004
14	60	003
15	78	002
16	80	2*
17	85	002
18	87	003
19	88	002
20	90	3+
21	100	003
22	128	004
23	130	003
24	136	004
25	137	003
26	154	002
27	160	003
28	162	002
29	163	003
30	166	002
31	167	003
32	168	002
33	169	2*
34	170	1*
35	175	2*
36	176	1*
37	177	2*
38	200	002
39	211	001
40	212	002
41	214	002
42	217	001
43	218	002
44	230	003
45	242	004
46	244	003
47	246	004
48	247	003
49	254	2*
50	255	003
51	256	004
52	258	003
53	267	002
54	270	004
55	276	003
56	280	004
57	302	005
58	303	004
59	310	005
60	311	004
61	312	005
62	314	004
63	315	005
64	330	006
65	331	005
66	344	005
67	346	005
68	349	005
69	354	005
70	370	004
71	375	005
72	376	004
73	380	005
74	385	004
75	386	005
76	399	004
77	420	005
78	421	004
79	430	004
80	437	003
81	448	004
82	449	003
83	450	004
84	457	003
85	458	004
86	498	005
87	511	006
88	513	005
89	520	005
90	530	005
91	532	004
92	534	004
93	535	005
94	537	005
95	553	005
96	562	006
97	563	005
98	565	006
99	570	006
100	580	006
101	588	007
102	590	007
103	597	008
104	600	004
105	601	005
106	602	004
107	605	005
108	606	004
109	610	005
110	618	004
111	620	005
112	622	005
113	624	004
114	625	005
115	633	005
116	644	005
117	660	005
118	664	006
119	666	005
120	668	006
121	681	005
122	683	006
123	700	006
124	701	005
125	703	006
126	704	005
127	705	006
128	710	006
129	712	005
130	713	006
131	716	005
132	718	006
133	719	005
134	730	006
135	733	006
136	743	005
137	744	006
138	769	007
139	770	006
140	772	006
141	780	007
142	781	006
143	782	007
144	786	006
145	788	007
146	789	006
147	790	007
148	791	006
149	793	007
150	795	006
151	797	007
152	807	006
153	809	007
154	820	007
155	832	008
156	834	007
157	835	008
158	840	007
159	841	008
160	845	007
161	846	008
162	850	008
163	855	008
164	859	008
165	863	008
166	865	007
167	870	007
168	873	007
169	889	008
170	893	008
171	897	008
172	900	008
173	910	008
174	930	008
175	962	8+
176	967	008
177	969	9+
178	970	008
179	988	008
Sheet1

Cell Formulas
Range	Formula
C2	C2	=VLOOKUP(B2,A6:B179,2,1)

 

[dfelmey]

Thank you, that worked. Can you please tell me how you easily got rid of all the second numbers and put in one long column? You sorted numerically, not sure if that was part of the formula you used to do that, or you just made sure they were sorted numerically. Thank you once again for the assistance. USPS comes out with a new version of this couple times per year. Would be nice if we could just do it easily each time. Thanks again!

 

[Scott Huish]

I cut and paste the sections one underneath each other.
Then I did a Text to Columns on the first column using - as the delimiter and checking treating consecutive delimeters as One.
Click Next. Click on the 2nd column and check Do Not Import Column (Skip). Click Finish.