# Comparing Latitude and Longitude to find Closest Distance

#### one3nine0

##### Board Regular
So i have a table like the one below (but much larger, showing multiple states):

Company IDStateZip CodeLatitudeLongitudeClosest Company ID Distance (miles)
37
AK9950361.1988159
-149.9060107
80AK9951861.17353626
-149.883512
230
AK99654
61.56331423
-149.2925177
353
AK99654
61.57614692
-149.4098359
270
AK
 99669

<colgroup><col></colgroup><tbody>
</tbody>
60.50016076
 -151.077

<colgroup><col></colgroup><tbody>
</tbody>
272AK
 99672

<tbody>
</tbody>
60.52622641
 -150.727

<tbody>
</tbody>
81AK9970164.836296-147.715406

<tbody>
</tbody>

Is there a formula or macro I can use that will give me the shortest distance to the nearest Company ID by Latitude and Longitude in an adjacent column?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi, yes you could use a formula to get the distance.
What would be the point's coordinates to compare with?

So the goal is to take this list, and for each company ID or location to find the nearest Company ID location and report the distance in the blank column.

I cannot find a way to make 1 formula to look and compare all the points to find the closest without having to individually compare each point and then just selecting the lowest value

Ok, I figured out how use an array to get what I need. I do get num errors with the ACOS function that I am not sure how to solve, and right now i am just performing those by name. Any insight into that would be really helpful

withe the earth radius set at 6371.1 km

Replies
6
Views
1K
Replies
12
Views
31K
Replies
6
Views
2K
Replies
3
Views
1K
Replies
0
Views
355

1,203,502
Messages
6,055,777
Members
444,823
Latest member
AnAverageGuy

### 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.

### Which adblocker are you using?

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