proximity of coordinate data

G’day again Excel forum

I’m having another little problem and I was hoping someone out there might have some thoughts.

I have a series of points in a 3D Cartesian layout, I’ve listed the points in a table array each with a Point ID, X value, Y value, Z value and an ‘N’ value. I’m trying to find an equation I can enter into a new column (H4) that can lookup the ‘Point ID’ of the nth closest point to the point on the subject row (and ideally be dragged down).

example.png

So for example in cell H4 the equation would find the 3rd closest point to point 1 (point 4, returning the value 4), while in cell H5 the equation would find the 4th closest point to point 2 (Point 6, returning the value 6).

I can calculate the distance between any 2 points thusly;

Distance between 2 points: (SQRT(((X1 – X2)^2)+ ((Y1 – Y2)^2)+ ((Z1 – Z2)^2)))

But I need an equation that will calculate the distance between the subject point and all points and find the nth smallest value.

Maybe and Array formula of some description?

my apologies, hopefully this makes things clearer, if it doesn't let me know and i will try to simplify it further... I've included an analogue to the calculations i need to make, hopefully that helps as well...