troubleshooting Question

# Help me find the 3-10 closest locations

Microsoft ExcelMicrosoft Office
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.
aikimark
Get vaccinated; Social distance; Wear a mask
Top Expert 2014
###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.