asked on # Help me find the 3-10 closest locations

Hello there,

I have 35,000 locations. I want a formula that will find the closest 3 points from the same list. There was a prior question and solution here, but I cannot get it to work:

https://www.experts-exchange.com/questions/28561261/Find-10-closest-x-y-coordinates-between-two-huge-x-y-data-set.html

Here's my data:

Unique identifiers:

$A$2:$A$35651

Latitude:

$J$2:$J$35651

Longitude:

$K$2:$K$35651

Based on the question above I modified the formula that looked like a solution:

=SUM(IF(SQRT(($J2-$J$2:$J$35651)^2+($K2-$K$2:$K$35651)^2)=SMALL(IF($J$2:$J$35651="","",SQRT(($J2-$J$2:$J$35651)^2+($K2-$K$2:$K$35651)^2)),2),$N$2:$N$35651,0))

This returns a #NUM! error.

Nearby locations are in LM, LN, and LO2. I doubt we'd need to reference those, but some people used them in the event of a tie. Breaking a tie is a nice feature, but ties in my dataset will be so rare that it's more of a "nice to have" if you see an easy solution.

It looks like they're mostly using the SMALL(array,k) to get the second-closest value, which I understand. I can easily change the K and lookup range, or you can use RIGHT(LM1,1)+1 to get 2 since the column is called NearbyPlace1, NearbyPlace2, etc. And I assume we'll start with 2 because 1 would just re-find the current location.

I have 35,000 locations. I want a formula that will find the closest 3 points from the same list. There was a prior question and solution here, but I cannot get it to work:

https://www.experts-exchange.com/questions/28561261/Find-10-closest-x-y-coordinates-between-two-huge-x-y-data-set.html

Here's my data:

Unique identifiers:

$A$2:$A$35651

Latitude:

$J$2:$J$35651

Longitude:

$K$2:$K$35651

Based on the question above I modified the formula that looked like a solution:

=SUM(IF(SQRT(($J2-$J$2:$J$

This returns a #NUM! error.

Nearby locations are in LM, LN, and LO2. I doubt we'd need to reference those, but some people used them in the event of a tie. Breaking a tie is a nice feature, but ties in my dataset will be so rare that it's more of a "nice to have" if you see an easy solution.

It looks like they're mostly using the SMALL(array,k) to get the second-closest value, which I understand. I can easily change the K and lookup range, or you can use RIGHT(LM1,1)+1 to get 2 since the column is called NearbyPlace1, NearbyPlace2, etc. And I assume we'll start with 2 because 1 would just re-find the current location.

Microsoft ExcelMicrosoft Office

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

I have recommended this question be closed as follows:

Accept: aikimark (https:#a42442417)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9

Experts-Exchange Cleanup Volunteer