ups zone chart management

[jonnybigood]

First off I apologize if this has already been figured out. I am trying to figure out a formula that takes a zip code and gives me the zone it falls into. Column A would contain a range that looks like 004-005 and the columns after in the same row would contain the zone. ex: Range Zones 384-385 007 307 207 247 137 107 386-392 006 306 206 246 136 106 393-418 007 307 207 247 137 107 420 006 306 206 246 136 106 421-422 007 307 207 247 137 107 Given the zip code is there a way to look thru these to give the correct zones without using VBA code? Any help would be appreciated. Thanks. Edited to try to show where each new row starts but it deletes my carriage returns. the range can be listed like 000-000 or just 000 if that makes any sense.

 

[RonB1111]

You can find a "list of zip codes by time zone" available for free and for a fee by googling this phrase. Here's a free excel list I found listing every 5 digit zip code with the time zone and city/state it covers. You'd then use the formula =vlookup() to pull timezones from this list.

http://www.mediafire.com/file/cvmmmf...ipcode222.xlsx

 

[jonnybigood]

Thankyou for replying but I am actually trying to calculate the shipping costs of packages being sent from one zip to another using UPS zone charts. Sorry for the confusion.

 

[FormR]

I bit of a guess, but may help.

Excel Workbook
	A	B	C	D	E
1	ZIP	Zone		Zip	Zone
2	000-010	zone 1		010	zone 1
3	011-018	zone 2		011	zone 2
4	019-080	zone 3		078	zone 3
Sheet1

 

[jonnybigood]

Thanks for the quick reply. That formula was exactly what I needed. Really appreciate your help.

 

[SteveInTexas]

Johnny Bi Good,

I'm needing to do the same thing - calculate UPS ground shipping costs for any two zip codes in the Lower 48. How did you get all the Zone charts? UPS.com only lets you down load one from zip code chart at a time and there are over 700 of them?

Steve

 

[kevinb11]

http://www.ups.com/content/us/en/sh...r+Shipping+Within+the+U.S.+and+to+Puerto+Rico

You can get the zones for your zip here.

 

[SteveInTexas]

That's what I mean, you have to enter one Origin zip at a time. FedX let's you download all Zips at once (over 700 files). I thought maybe you found a way to do that on UPS.
Thank you for replying!

 

[blodatoUPS]

I bit of a guess, but may help.

Sheet1

	A	B	C	D	E
1	ZIP	Zone		Zip	Zone
2	000-010	zone 1		010	zone 1
3	011-018	zone 2		011	zone 2
4	019-080	zone 3		078	zone 3

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 {=INDEX($A$2:$B$4,MATCH(D2,LEFT($A$2:$A$4,3)),2)} E3 {=INDEX($A$2:$B$4,MATCH(D3,LEFT($A$2:$A$4,3)),2)} E4 {=INDEX($A$2:$B$4,MATCH(D4,LEFT($A$2:$A$4,3)),2)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

When i try and use these formulas, I keep receiving an error. What format does the ZIP range need to be in for column A (text,general,number?). Likewise what format does the search zip in column D need to be in on the example above?