Expert level: Proximity matching for latitude/longitude

aallaman

New Member
Joined
Dec 4, 2009
Messages
44
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.781383

<tbody>
</tbody>
-122.460533

<tbody>
</tbody>

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

LocationLatitudeLongitude
Denver
40.395369

<tbody>
</tbody>
-104.756684
Chicago
41.92975

<tbody>
</tbody>
-87.7467
Cleveland
41.418133

<tbody>
</tbody>
-81.787063
Asheville
35.53621

<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:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Location</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Latitude</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Longitude</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Closest City 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Closest City 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Closest City 3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">San Fran</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">37.78138</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-122.460533</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Location</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Latitude</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Longitude</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Latitude2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Longitude2</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Denver</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">40.39537</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-104.756684</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">2.613986</td><td style="text-align: right;border-top: 1px solid black;;">17.703849</td><td style="text-align: right;;">20.317835</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Houston</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">29.6795</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-95.538717</td><td style="text-align: right;border-left: 1px solid black;;">-8.101883</td><td style="text-align: right;;">26.921816</td><td style="text-align: right;;">35.023699</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Chicago</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">41.92975</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-87.7467</td><td style="text-align: right;border-left: 1px solid black;;">4.148367</td><td style="text-align: right;;">34.713833</td><td style="text-align: right;;">38.8622</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Asheville</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">35.53621</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-82.60152</td><td style="text-align: right;border-left: 1px solid black;;">-2.245173</td><td style="text-align: right;;">39.859013</td><td style="text-align: right;;">42.104186</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Cleveland</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">41.41813</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-81.787063</td><td style="text-align: right;border-left: 1px solid black;;">3.63675</td><td style="text-align: right;;">40.67347</td><td style="text-align: right;;">44.31022</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D6</th><td style="text-align:left">=B6-$B$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E6</th><td style="text-align:left">=C6-$C$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F6</th><td style="text-align:left">=ABS(<font color="Blue">D6</font>)+ABS(<font color="Blue">E6</font>)</td></tr></tbody></table></td></tr></table><br />
 

aallaman

New Member
Joined
Dec 4, 2009
Messages
44
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,194
Messages
5,473,075
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top