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

Address1	City	State	Zip	Latitude	Longitude
119 TINE LANE	NEW MARKET	AL	35761	34.83257949	-86.46106284
1177 LEWIS MTN. RD	GUNTERSVILLE	AL	35976	34.42853529	-86.30492038
2980 COUNTY RD 53	TUSKEGEE	AL	36083	32.47986191	-85.64240964
617 CHERRY ST NW	DECATUR	AL	35601	34.61583017	-86.99362234
2300 HACKBERRY LANE List B	HOOVER	AL	35226	33.42695376	-86.82373959

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Office ID	DBA	Latitude	Longitude
AR302	1st Corner	35.22692	-90.78813873
AR302	1st Corner	35.22541	-90.788153
AR302	1st Corner	35.82997	-90.67296349
AR302	1st Corner	35.22169	-90.82464634
AR302	1st Corner	35.86746	-90.71383514
AR302	1st Corner	35.84989	-90.64110695
MO306	Advantage	38.56485	-90.38050127
MO306	Advantage	38.58855	-90.350542
MO306	Advantage	38.55125	-90.39158163
MO306	Advantage	38.60448	-90.37092379

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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))

Address1	Address2	City	State	Zip	Phone	Birth Year	Broker ID	DBA	Latitude	Longitude	Closest Affiliate	Dist. In Miles
12110 JOHNS RD		ANCHORAGE	AK	99515	9073516078	1964			61.110885	-149.879453	WA303-001	2300
134 MATTHEW PAUL WAY	ANCHORAGE	AK	99504	9073069132	1982			61.22259479	-149.761955	WA303-001	2300
19301 VILLAGES SCENIC PARKWAY	ANCHORAGE	AK	99516	9072768008	1961			61.104106	-149.778328	WA303-001	2294
13001 FOSTER RD		ANCHORAGE	AK	99516	9073069379	1973			61.10319066	-149.7478848	WA303-001	2293
3412 MCKENZIE DR		ANCHORAGE	AK	99517	9078879176	1966			61.18956977	-149.9508385	WA303-001	2307

<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

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