Filling in mail merge fields geographically in Excel

In order for the technique on this page to work, you must purchase and install Spheresoft Zip Code Distance and Radius Tools

Sometimes you want to do a direct mail campaign using mail merge, but you want one of the fields to be based on the closest location of something (a branch, a store location, a salesperson, etc). Here's a way to set that up in Excel -- then you can use your word processor to read in this data for the letter.

In this example, we want to tell the prospect where the closest branch office is. Our customer spreadsheet looks something like this:

On another sheet in this same file, we could have this data:

Since we are going to use a VLOOKUP in this data later, it must be sorted in Zip Code order. Also, it helps if the Zip Codes are stored as text by prepending a single quote (') in front of each one (or by using Excel's TEXT formula).

Follow these steps to put the closest branch next to each customer:

1. On the customer sheet, in cell E1 put the text Closest Branch Zip
2. In cell E2 put the formula =ZipCodesInListWithinDistance(D2, Sheet2!$A$2:$A$6,6000)
   In this example, the branch zip codes are on Sheet2, in cells A2:A6. The dollar signs let me copy this formula without the location of the branch zips changing.
3. Copy E2 to E3:E10.

It should look like this:

ZipCodesInListWithinDistance is a function you get with Zip Code Tools. It returns the subset of zip codes from a list that are within a given distance from the target zip code. Since we have entered it into a single cell (and not a range), it returns just the closest one.

Now, here is how we look up the branch:

1. On the customer sheet, in cell F1 put the text Closest Branch
2. In cell F2 put the formula =VLOOKUP(E2, Sheet2!$A$2:$B$6, 2)
   This works because the branch zip codes are sorted and are formatted as text. If your zip codes are formatted as numbers, you can use Excel's TEXT function to convert them.
3. Copy F2 to F3:F10.

It should look like this:

If you have Spheresoft Zip Code Tools installed, download this sheet to see a live example.

(Back to the ZipCodeTools home page)

$65 - immediate electronic delivery

Not what you were looking for? Please send us your feedback!