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
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