Nearest neighbour limited by point type

Hi all,

I have a list of x and y xoordinates all with a respective ID and point type (either "RO" or "RP"). I wish to define the nearest other point by ID for every point but I wish to limit this calculation to only points of the opposite type. That is, if I have point ID 315 which is of RO type, I wish to find the nearest point that is of RP type, and visa-versa.

So far through reading many forums and posts and spending quite some time editing I have managed to create a solution for finding the nearest other point but not limit this to the opposite point type. My scenario is as follows...

	M	N	O	P	Q
1	RO	311	478113	6771180	312
2	RO	312	478105	6771179	?
3	RO	313	478096	6771178	?
4	RP	314	478088	6771177	?
5	RP	315	478080	6771176	?
6	RP	316	478071	6771175	?

Where... M=Type, N=ID, O=X-coordinate, P=Y-coordinate, Q=Nearest Point

The table shown consists of cells M1:Q7 but the entire table I wish to select from will pontentially be M1:Q1000. Obviously I cannot have 312 as the nearest ID to 311 as it must be of RP type. Please note that the table shown shows rounded coordinates, the spreadsheet I use has the exact coordinate to 6 decimal places. The formula I have used to establish the Nearest ID as used in Q2 is as follows.

Formula:

{=INDEX($N$2:$N$1000,MATCH(MIN(IF(O2&","&P2<>O$2:O$1000&","&P$2:P$1000,ABS(O2-O$2:O$1000)+ABS(P2-P$2:P$1000))),IF(O2&","&P2<>O$2:O$1000&","&P$2:P$1000,ABS(O2-O$2:O$1000)+ABS(P2-P$2:P$1000)),0))}

Although it may be considered quite a complex formula I am still only learning the ropes of excel. I must acknowledge all sources that have helped me get this far but would just like the finishing touch to have it do what I need. I have considered a macro however the spreadsheet I am producing, for reasons beyond my control is required to be without a macro. Im sure that it is probably just a simple IF statement that needs to be inserted but I am not sure where and how.

Thanks in advance!

Kind Regards,
Ben

Try:

{=INDEX($N$2:$N$1000,MATCH(MIN(IF((O2&","&P2<>O$2:O$1000&","&P$2:P$1000)*(Q2<>Q$2:Q$1000),ABS(O2-O$2:O$1000)+ABS(P2-P$2:P$1000))),IF((O2&","&P2<>O$2:O$1000&","&P$2:P$1000)*(Q2<>Q$2:Q$1000),ABS(O2-O$2:O$1000)+ABS(P2-P$2:P$1000)),0))}

By the way - you check that O and P are different. Does it mean you can have two points with exactly the same pair of coordinates? if not, shorter version will be quicker:

{=INDEX($N$2:$N$1000,MATCH(MIN(IF(Q2<>Q$2:Q$1000,ABS(O2-O$2:O$1000)+ABS(P2-P$2:P$1000))),IF(Q2<>Q$2:Q$1000,ABS(O2-O$2:O$1000)+ABS(P2-P$2:P$1000)),0))}

And I am not sure about application, but in most of them, cartesian distance would be more appropriate than using

ABS(O2-O$2:O$1000)+ABS(P2-P$2:P$1000)

so:

(O2-O$2:O$1000)^2+(P2-P$2:P$1000)^2

(as you do not calculate distance, but only locate smallest one, no need for SQRT here).

Sure, not Q but M. Good you spotted this
So if O and P are some mesurements (this is how I understand coordinates) in 2 perpendicular directions, I'd for sure recommend

(O2-O$2:O$1000)^2+(P2-P$2:P$1000)^2

(twice)

have a look on
0 0
and
0 5
5 0
3 3
The closest one to 0,0 will be 3,3 (some 4.25 units away)

Glad to hear that. And thanks for reputation point