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]

 

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