Formula to determine USPS Postal Zone based on ZIP

Question

I'm looking to make the sorting of hundreds of envelopes by USPS postal zones easier. The difficulty is in having to manually sort them for each of the 8 US zones, based on the origin ZIP code: 91352.

http://postcalc.usps.gov/ZoneCharts/

I have a spreadsheet of contacts that includes a ZIP column. I've set up a separate sheet of all of the USPS ZoneCharts site based on "913", and combined the four sections into one (two columns total). I then used the LEFT and RIGHT functions to get the first three and last three numbers of the first column and put them into their own columns (now three columns total):

ZIP_BEG  ZIP_END  Zone
005      098      8
100      212      8
214      268      8
270      342      8
344      344      8
346      347      8
349      349      8
350      352      7
354      359      7
360      361      8
362      362      7
363      364      8
365      366      7
...etc.

Would I use the VLOOKUP function from the sheet of contacts to search each ZIP (using the LEFT function to use only the first three numbers of each ZIP value) and then check whether that value is both greater than the ZIP_BEG value and less than the ZIP_END value?

Whatever row it matches, it would return the Zone value. I'm putting this as a column after the ZIP column in the first sheet.

Answer 1

Splitting out every possible zip code seems viable but might be ‘overkill’ (though might be useful to detect errors). I’m assuming that a code not in the range mentioned is (a) not valid but (b) does not require to be flagged in any way, so for example 099 will either never arise in practice (unless the tables are updated) or can ‘safely’ be treated as 098.

This is to make it possible to consider only one value for each band (before a change in Zone), conveniently your ZIP_BEG ones, in conjunction with an inexact VLOOKUP. The syntax for VLOOKUP is:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

where the fourth parameter (range_lookup) is optional. Forgetting it or setting it to TRUE (or 1) by mistake has caused much grief but may be very suitable here.

To quote:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value.

(It does a binary search, so needs to know which direction is more and which less).Your values should already be in the required order, so a formula such as:

=VLOOKUP(A2,ZIPUP,2)  

somewhere in the same workbook should be sufficient, where the value to be looked up (first three digits of destination Zip) is assumed to be in A2 and ZIPUP is the name of a workbook-scoped range of ZIP_BEG in one column and Zone in the matching rows in the column immediately to the right of that.

Given the initial assumptions, the entire ZIP-BEG list is not required (108 ‘ranges’) as, using only the limits, 74 are sufficient (and should be quicker).

If not aggregating the bands in that way, beware of formatting as 005 is not the same as 5 and that distinction is relevant to =VLOOKUP. You have used =LEFT and =RIGHT to extract your lists and these text functions return strings, though here I would prefer number formatting myself. (I split the ranges with Text to Columns.)