We’ve just added a new feature to CDXZipStream, our Microsoft Excel add-in for location and ZIP code analysis. You can now find the second- and third-closest ZIP codes to a target ZIP (in addition to the closest) using the function CDXClosestZIP. This function is available with the Lite, Basic, and Premium demographic versions of CDXZIPStream, and can be evaluated as part of the free 30-day demo of the software.
This functionality can be used for a variety of tasks: identifying the closest stores to customers, the closest NFL teams to sports merchandising stores, the nearest warehouse operations to retail outlets. It works very quickly and effectively for long lists of locations, and does not require complete address information; only a ZIP code is required for each location.
Here’s how it works: open an Excel workbook that contains the locations (or ZIP codes) of interest. In this example, we have a list of store and customer ZIP codes:
Right-click on any cell in the worksheet, and from the CDXZipStream drop-down menu select the function CDXClosestZip. Based on the cell locations of the data above, we use the following input to find the closest ZIP code to each customer:
We could input “49224” as the first target ZIP code, but since we want to eventually apply this function to the entire list of customer ZIP’s, we use the cell address (cell C2) of the first customer instead. Also, when we specify that the range of the store ZIP codes, we use absolute referencing with dollar signs (“$B$2:$B$200”) so this range cannot change when we copy the resulting formula:
=CDXClosestZip(C2,0,$B$2:$B$200)
This returns the ZIP code 48911 for the first customer in the list. Now we just copy this formula down the entire list of customers, and CDXZipStream finds the closest ZIP code for each:
To find the second- or third-closest ZIP codes, we repeat this process while selecting these as the returned data
The straight-line (as the crow flies) distance can also be selected as the returned data, for the closest, second- and third-closest ZIP codes.
For a quick tutorial in using CDXClosestZIP, please refer to the YouTube video Find the Closest ZIP Code to a Target ZIP, or the video below. This tutorial also shows how you can use the Excel VLOOKUP function to find other identifying data (such as store ID) for the returned closest ZIP’s.