For a general overview of how to insert data and functions into your worksheet, please refer to the topic Inserting Data and Functions.
CDXClosestZip determines which ZIP Codes are closest, second-closest, or third-closest to a target ZIP Code. It will also calculate the distance from the 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.)
This function will work with Canadian postal codes, if you have purchased the Canadian data option.
You can also refer to the Youtube video Find the Closest ZIP Code to a Target ZIP for a demonstration of the CDXClosestZip function.
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, second-closest, or third-closest ZIP Code, or the distance to these ZIP Codes in miles, kilometers, or nautical miles. The closest ZIP Code 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.