Expert level: Proximity matching for latitude/longitude

aallaman

New Member
Hi Excel Geniuses

I've got a doozy of a question. I'm trying to take a center point (lat/long) and then use that value to reference a table to find the three closest matches by lat/long proximity.

Here is a simplified example:

The first spreadsheet has the centering location value and we want to populate the three closest cities from referencing the second table
LocationLatitudeLongitudeClosest City 1Closest City 2Closest City 3
San Fran
 37.7814

<tbody>
</tbody>
-122.460533

<tbody>
</tbody>

The second table is thousands of rows long and has one lat/long per row

LocationLatitudeLongitude
Denver
 40.3954

<tbody>
</tbody>
-104.756684
Chicago
 41.9297

<tbody>
</tbody>
-87.7467
Cleveland
 41.4181

<tbody>
</tbody>
-81.787063
Asheville
 35.5362

<tbody>
</tbody>
-82.60152
Houston
 29.6795

<tbody>
</tbody>
-95.538717

<tbody>
</tbody>

Any idea how to use either a formula or VBA to make these proximity matches??

Thanks!!!

Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Comfy

Well-known Member
I have no idea but gonna through out an idea anyway.

Would it be safe to say that if we sum the difference between each location Lat and each location Long and rank them by the result, that the lowest value would be the closest?

So I've not added a lookup yet but just sorted using the above logic.

Is this even remotely correct? Sorry not from the States so couldn't really check.

aallaman

New Member
Wow you all are awesome!

@Comfy, looks like that might work

@AngelJ, that is a work of art!

@shg, thanks for the link I'll dig into that script.

Replies
3
Views
215
Replies
4
Views
538
Replies
8
Views
2K

1,133,148
Messages
5,657,109
Members
418,355
Latest member
michaelirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back