Speculative thinking, say you have a list of 615 addresses. How do you find the nearest three locations to each address?

[RockandGrohl]

Let's say you have 1,400 postcodes that correspond to a point on a map. Using a location local(ish) to me, in the UK I have 615 locations across England. I want to find the nearest three for each of the 615 locations, in that same list.

So as an example we choose Gloucester. Geographically near Gloucester in my list of 615 addresses, we have Cheltenham, Stroud, Tewkesbury, and then further afield are Cirencester, Ross-on-Wye, Ledbury.

The closest three are Cheltenham, Stroud & Tewkesbury and I would like this to be reflected. The only information I have is postcode, so this would need to link in to mapping functionality somehow. I'm just wondering if anyone does anything similar and whether this technique is used in anyone's existing processes.

Thanks!

 

[RockandGrohl]

it's most likely the rounding off errors, as you can see I rounded off Col. E and the 3 min distances to the same ROUND(...,6) so that they can be matched by the index/match function

So a sample of the figures is here:

Distance 1	Distance 2
0​	0​
379.1678​	5.595278​
108.8417​	1.579212​
55.52662​	0.948751​
92.49206​	2.158886​
262.3064​	3.801827​
147.866​	2.30678​
220.8747​	5.076015​
136.2451​	2.083453​
75.25711​	1.647548​

If I play with the num_digits, I can't seem to get a match whichever way I cut it unfortunately.

 

[RockandGrohl]

So a sample of the figures is here:

If I play with the num_digits, I can't seem to get a match whichever way I cut it unfortunately.

Sorry, I read what you said, but just didn't parse it. Noticed there was a ROUND formula on your distance calculation, so applied that to the one I'm using and then index-matching based on that, exactly what I needed.