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:
Here's my data:
Based on the question above I modified the formula that looked like a solution:
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.