Need to compare Latitude & Longitude coordinates to find closest office

bbrimberry

New Member
Joined
Mar 23, 2016
Messages
11
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

kwadjo

New Member
Joined
Jun 29, 2010
Messages
9
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,152
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top