CDXClosestZip determines which zip code (from a specified list) is closest to a target zip code. CDXClosestZip can be employed as a right-click function or input directly into your worksheet as a custom function formula. (Please also refer to the help section on custom functions.) If you have purchased the Canadian postal code database, you can also use it to find the closest postal code to a target.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXClosestZip Function, and you will see the input box shown below:
The target zip code can either be specified using the actual zip code or the worksheet cell location (such as A1 or $A$1) of the target zip code. In the case above, we want to find which zip code in the range B3:B12 is closest to the target zip 61818. The zip list range can also be defined as an Excel named range. For example, if we define B3:B12 as a range called "ZipCodes", "ZipCodes" can be used in the zip list range input box. The returned data can be the closest zip code, or the distance to the closest zip code in miles, kilometers, or nautical miles. The result from the example above is:
The zip code 61820 (from the list in range B3:B12) is closest to the target zip 61818.
To apply the CDXClosestZip function to a list of target zips, it can be input directly to the worksheet in a custom function formula. For example, let's say we have a list of store locations and a list of customers, and want to determine which store is closest to each customer. We will define our list of store zip codes (in range B3:B8) using the name "StoreZips".
The CDXClosestZip function is input as a custom function formula in the following generalized form:
=CDXClosestZip(Target Zip Code, Returned Data Option, Zip List Range)
The Returned Data Option is specified as follows:
The equation in cell D3 is
=CDXClosestZip(C3, 0, StoreZips)
The target zip code is 08009, the zip code of the first customer which is located in cell C3. The result returned to this cell will be the closest zip code found in the range we have named "StoreZips"
The store zip code closest to the first customer is 08053:
To apply this function to the rest of the data, copy and paste the custom function formula. Each customer zip code will be the target zip code for the CDXClosestZip calculation. In this case, we copy cell D3 and paste it to the range D4 through D18:
This cut and paste technique will also work if the data are listed in rows instead of columns as shown here.