# Calculate UPS Time In Transit Based on Zip Code

#### ngarnold

##### New Member
I'm trying to construct a formula that will calculate time in transit for UPS. I have a sheet "Sheet1"with tab that has a column that displays the five digit zip code "zip", and I have another tab "Sheet2" in that spreadsheet with a column for "Ground" (days in transit) and "Dest. ZIP"

The destination zip codes on Sheet2 are given as the first three digits of the zip code and (sometimes) in ranges, i.e 150-153 would represent zip codes 15000 to 15399. These are not in order, ex: 407-409 = 3, 260-261 = 2, 036 = 4

The columns on Sheet2 look like this (for example):

I want to make a formula that queries the cell "zip" on Sheet1, and returns a value "Ground" from Sheet2.

So if the zip code on Sheet1 in cell A2 is 46777, the formula would display 2 for the number of days in transit. If the zip code in Sheet1 A3 were 21525, the formula would display 3, if the zip is 14200 in Sheet1 A4 then 3...

Not sure if leading zeros on zip codes will cause an error? Presently those cells are set to display as plain text.

You can get the zone chart from UPS website here: https://www.ups.com/content/us/en/s...r+Shipping+Within+the+U.S.+and+to+Puerto+Rico

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
I hope I understood your objective.

The Dest_ZIP characters in column B are text and must be entered so (for example, the 215 is actually entered as '215); they also must be either 3 characters long or 7 characters long. The look-up values in column D are numbers and must be entered so.

Try this formula which relies on an array operation:

ABCDE
1GroundDest_ZIPlook-upGround
22464-473467772
33140-143215253
43147142003
53154-16836254
63215
73407-409
82260-261
94036

<tbody>
</tbody>
Sheet5

Array Formulas
CellFormula
E2{=INDEX(\$A\$2:\$A\$9,MATCH(TRUE,(\$D2>=100*LEFT(\$B\$2:\$B\$9,3))=(\$D2<=IF(LEN(\$B\$2:\$B\$9)=3,100*LEFT(\$B\$2:\$B\$9,3)+99,RIGHT(\$B\$2:\$B\$9,3)*100)),0))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Last edited:

Replies
0
Views
215
Replies
6
Views
12K
Replies
3
Views
3K
Replies
16
Views
1K
Replies
6
Views
710

1,211,710
Messages
6,103,451
Members
447,866
Latest member
bowers261

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back