Need to compare Latitude & Longitude coordinates to find closest office

[PaulCL]

I have a list of 20,000 addresses(List A) and another list of 2,500 offices(List B) that are both geocoded. I need to determine, in miles, which of the 20,000 addresses are closest to which 2,500 offices. Is there an excel formula that would help me solve this?
Thanks!

Sample

List A
[TABLE="width: 792"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Address1[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[/TR]
[TR]
[TD]119 TINE LANE[/TD]
[TD]NEW MARKET[/TD]
[TD]AL[/TD]
[TD]35761[/TD]
[TD]34.83257949[/TD]
[TD]-86.46106284[/TD]
[/TR]
[TR]
[TD]1177 LEWIS MTN. RD[/TD]
[TD]GUNTERSVILLE[/TD]
[TD]AL[/TD]
[TD]35976[/TD]
[TD]34.42853529[/TD]
[TD]-86.30492038[/TD]
[/TR]
[TR]
[TD]2980 COUNTY RD 53[/TD]
[TD]TUSKEGEE[/TD]
[TD]AL[/TD]
[TD]36083[/TD]
[TD]32.47986191[/TD]
[TD]-85.64240964[/TD]
[/TR]
[TR]
[TD]617 CHERRY ST NW[/TD]
[TD]DECATUR[/TD]
[TD]AL[/TD]
[TD]35601[/TD]
[TD]34.61583017[/TD]
[TD]-86.99362234[/TD]
[/TR]
[TR]
[TD]2300 HACKBERRY LANE

List B[/TD]
[TD]HOOVER[/TD]
[TD]AL[/TD]
[TD]35226[/TD]
[TD]33.42695376[/TD]
[TD]-86.82373959[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 421"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Office ID[/TD]
[TD]DBA[/TD]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[/TR]
[TR]
[TD]AR302[/TD]
[TD]1st Corner[/TD]
[TD]35.22692[/TD]
[TD]-90.78813873[/TD]
[/TR]
[TR]
[TD]AR302[/TD]
[TD]1st Corner[/TD]
[TD]35.22541[/TD]
[TD]-90.788153[/TD]
[/TR]
[TR]
[TD]AR302[/TD]
[TD]1st Corner[/TD]
[TD]35.82997[/TD]
[TD]-90.67296349[/TD]
[/TR]
[TR]
[TD]AR302[/TD]
[TD]1st Corner[/TD]
[TD]35.22169[/TD]
[TD]-90.82464634[/TD]
[/TR]
[TR]
[TD]AR302[/TD]
[TD]1st Corner[/TD]
[TD]35.86746[/TD]
[TD]-90.71383514[/TD]
[/TR]
[TR]
[TD]AR302[/TD]
[TD]1st Corner[/TD]
[TD]35.84989[/TD]
[TD]-90.64110695[/TD]
[/TR]
[TR]
[TD]MO306[/TD]
[TD]Advantage[/TD]
[TD]38.56485[/TD]
[TD]-90.38050127[/TD]
[/TR]
[TR]
[TD]MO306[/TD]
[TD]Advantage[/TD]
[TD]38.58855[/TD]
[TD]-90.350542[/TD]
[/TR]
[TR]
[TD]MO306[/TD]
[TD]Advantage[/TD]
[TD]38.55125[/TD]
[TD]-90.39158163[/TD]
[/TR]
[TR]
[TD]MO306[/TD]
[TD]Advantage[/TD]
[TD]38.60448[/TD]
[TD]-90.37092379[/TD]
[/TR]
</tbody>[/TABLE]

 

[RatExcel]

Try this (use CTRL + SHIFT + ENTER while entering the formula):

List A - columns A:F
List B - columns H:K

=INDEX($A$3:$A$7,MATCH(SMALL((ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),1),(ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),0))

Sample Workbook:

https://app.box.com/s/uye7cps3i7q4usv6z8iuwq9wsvqzzcxc

 

[DRSteele]

Very cool. But he wanted to know the distances too, so I noddled that one out.

Try this, in Kilometers (make sure it's CTRL+SHIFT+ENTER): {=INDEX(ACOS(COS(RADIANS(90-$E$3:$E$7)) *COS(RADIANS(90-J3)) +SIN(RADIANS(90-$E$3:$E$7)) *SIN(RADIANS(90-J3)) *COS(RADIANS($F$3:$F$7-K3)))*6371,MATCH(SMALL((ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),1),(ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),0))}

Credit is due here: BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)

 

[PaulCL]

Try this (use CTRL + SHIFT + ENTER while entering the formula):

List A - columns A:F
List B - columns H:K

=INDEX($A$3:$A$7,MATCH(SMALL((ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),1),(ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),0))

Sample Workbook:

https://app.box.com/s/uye7cps3i7q4usv6z8iuwq9wsvqzzcxc

The sample workbook was right on, but I would like it in reverse (List A v List B) where the result would be Office ID.
Thanks

 

[DRSteele]

This should switch the two tables:

Excel 2012
	Q	R
3	AR302	395.6233
Sheet36

Cell Formulas
Range	Formula
Q3	{=INDEX($H$3:$H$12,MATCH(SMALL((ABS($J$3:$J$12-E3)^2+ABS($K$3:$K$12-F3)^2)^(0.5),1),(ABS($J$3:$J$12-E3)^2+ABS($K$3:$K$12-F3)^2)^(0.5),0))}
R3	{=INDEX(ACOS(COS(RADIANS(90-E3)) *COS(RADIANS(90-$J$3:$J$12)) +SIN(RADIANS(90-E3)) *SIN(RADIANS(90-$J$3:$J$12)) *COS(RADIANS(F3-$K$3:$K$12)))*6371,MATCH(SMALL((ABS($J$3:$J$12-E3)^2+ABS($K$3:$K$12-F3)^2)^(0.5),1),(ABS($J$3:$J$12-E3)^2+ABS($K$3:$K$12-F3)^2)^(0.5),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[PaulCL]

Thanks for the extra formulas Mr. Steel. However, when I plugged in the formulas, the Dist In Miles was a little suspect(further than expected), and since there were identical results for the first two records. Here is a better sample of my spreadsheet.
I have the list of affiliates in a separate tab called "Affiliates" with the coordinates of the affiliates. I have omitted a few columns for this post.

Formula in U2: =INDEX(Affiliates!$A$2:$A$1128,MATCH(SMALL((ABS(Affiliates!$D$2:$D$1128-S2)^2+ABS(Affiliates!$E$2:$E$1128-T2)^2)^(0.5),1),(ABS(Affiliates!$D$2:$D$1128-S2)^2+ABS(Affiliates!$E$2:$E$1128-T2)^2)^(0.5),0))
Formula in V2: =INDEX(ACOS(COS(RADIANS(90-S2)) *COS(RADIANS(90-Affiliates!$D$2:$D$1128)) +SIN(RADIANS(90-S2)) *SIN(RADIANS(90-Affiliates!$D$2:$D$1128)) *COS(RADIANS(T2-Affiliates!$E$2:$E$1128)))*6371,MATCH(SMALL((ABS(Affiliates!$D$2:$D$1128-S2)^2+ABS(Affiliates!$E$2:$E$1128-T2)^2)^(0.5),1),(ABS(Affiliates!$D$2:$D$1128-S2)^2+ABS(Affiliates!$E$2:$E$1128-T2)^2)^(0.5),0))

[TABLE="width: 1064"]
<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Address1[/TD]
[TD]Address2[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Phone[/TD]
[TD]Birth Year[/TD]
[TD]Broker ID[/TD]
[TD]DBA[/TD]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[TD]Closest Affiliate[/TD]
[TD]Dist. In Miles[/TD]
[/TR]
[TR]
[TD]12110 JOHNS RD[/TD]
[TD][/TD]
[TD]ANCHORAGE[/TD]
[TD]AK[/TD]
[TD]99515[/TD]
[TD]9073516078[/TD]
[TD]1964[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]61.110885[/TD]
[TD="align: right"]-149.879453[/TD]
[TD] WA303-001[/TD]
[TD]2300[/TD]
[/TR]
[TR]
[TD="colspan: 2"]134 MATTHEW PAUL WAY[/TD]
[TD]ANCHORAGE[/TD]
[TD]AK[/TD]
[TD]99504[/TD]
[TD]9073069132[/TD]
[TD]1982[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]61.22259479[/TD]
[TD="align: right"]-149.761955[/TD]
[TD] WA303-001[/TD]
[TD]2300[/TD]
[/TR]
[TR]
[TD="colspan: 2"]19301 VILLAGES SCENIC PARKWAY[/TD]
[TD]ANCHORAGE[/TD]
[TD]AK[/TD]
[TD]99516[/TD]
[TD]9072768008[/TD]
[TD]1961[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]61.104106[/TD]
[TD="align: right"]-149.778328[/TD]
[TD] WA303-001[/TD]
[TD]2294[/TD]
[/TR]
[TR]
[TD]13001 FOSTER RD[/TD]
[TD][/TD]
[TD]ANCHORAGE[/TD]
[TD]AK[/TD]
[TD]99516[/TD]
[TD]9073069379[/TD]
[TD]1973[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]61.10319066[/TD]
[TD="align: right"]-149.7478848[/TD]
[TD] WA303-001[/TD]
[TD]2293[/TD]
[/TR]
[TR]
[TD]3412 MCKENZIE DR[/TD]
[TD][/TD]
[TD]ANCHORAGE[/TD]
[TD]AK[/TD]
[TD]99517[/TD]
[TD]9078879176[/TD]
[TD]1966[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]61.18956977[/TD]
[TD="align: right"]-149.9508385[/TD]
[TD] WA303-001[/TD]
[TD]2307[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Perhaps your issue arises because the distance is kilometers. The radius of the sphere Earth is 6371 km, or so. Convert to miles, or chains, rods, cubits, kalicams, leagues...whatever you like. Lol.

 

[PaulCL]

That make sense!
Thanks, aye.

 

[bbrimberry]

Hey guys,

great post. i just found this and want to calculate the closest office from a list.

i have modified the formula a bit to account for my list of about 1200 any reason i would be getting a value error?

i downloaded the sample and just replaced the lat long with my values.

the formula i am using is as follows


=INDEX($A$3:$A$1200,MATCH(SMALL((ABS(J3-$E$3:$E$1200)^2+ABS(K3-$F$3:$F$1200)^2)^(0.5),1),(ABS(J3-$E$3:$E$1200)^2+ABS(K3-$F$3:$F$1200)^2)^(0.5),0))

 

[DRSteele]

I see that all the formulas here require CtrlShiftEnter to invoke them. Try that and then copy the formula downward as needed.