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>

 

[bbrimberry]

i am at a loss to why this fomula is not working. i read previously about how to invoke the formula. if I use the original formula =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)) with control **** enter i have no problem.

if i correct the formula for my dataset =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)) i get a value error.

all i changed to the 7's to 1200 to account for my dataset.
I am grateful for any other thoughts or ideas on this.

 

[bbrimberry]

I found the problem. I had a hole in my dataset.

 

[kwadjo]

This is fantastic. It works great for me. I have one question though. How could I adapt this formula to find the nth closest affiliate to a given lat/long? (I really just want to find the top 5)

Thanks!