Need to compare XY coordinates from two data sets to find the closest point from one data set compared to the other

[Gary1234]

Hi All,

I have two data sets, lets call them set A and set B. Both have XY coordinates, the scale of which is micrometres (these are coordinates of individual cells in a 2D microscopy image).

I'm trying to find which cell from set B is closest to each cell in set A, and also get the exact distance they are apart. I.e. I need to compare every single coordinate in set A to every single coordinate in set B, and find the closest from set B to each one in set A.

I've seen this question answered before in the context of latitude and longitude coordinates (see here: Need to compare Latitude & Longitude coordinates to find closest office)

This was a suggested formula for doing this: =INDEX(ACOS(COS(RADIANS(90-$E$3:$E$7)) *COS(RADIANS(90-J3)) +SIN(RADIANS(90-$E$3:$E$7)) *SIN(RADIANS(90-J3)) *COS(RADIANS($F$3:$F$7-K3)))*6371,MATCH(SMALL((ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),1),(ABS(J3-$E$3:$E$7)^2+ABS(K3-$F$3:$F$7)^2)^(0.5),0))

However, this formula is controlling for the earths radius, which I do not need to do. My XY coordinates are simply points in a 2D space and the difference between each X and Y point is just micrometres.

Does anyone know how I can modify this formula so I can apply this to my data? I'm a complete newb at this, so cannot figure this out!!

I've attached some sample data in an image if that helps.

 

[Snakehips]

@Gary1234 Does this help?

Book1
	A	B	C	D	E	F	G	H	I	J
1	A			B					Nearest B	Nearest
2	X	Y	Ref	X	Y			A Ref	Distance	B Ref
3	4074.1	565.07	1	4326.3	536.84			1	141.6880591	3
4	4023.2	578.48	2	4453	560.51			2	191.4627118	3
5	4031.4	586.93	3	4214.6	583.38			3	183.2343922	3
6	4180.7	613	4					4	45.0172678	3
7	4083.9	617.53	5					5	135.0877955	3
8	4328.8	641.41	6					6	104.59988	1
Sheet9

Cell Formulas
Range	Formula
I3:I8	I3	=MIN(SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)))
J3:J8	J3	=MATCH(I3,SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Gary1234]

@Gary1234 Does this help?

Book1
	A	B	C	D	E	F	G	H	I	J
1	A			B					Nearest B	Nearest
2	X	Y	Ref	X	Y			A Ref	Distance	B Ref
3	4074.1	565.07	1	4326.3	536.84			1	141.6880591	3
4	4023.2	578.48	2	4453	560.51			2	191.4627118	3
5	4031.4	586.93	3	4214.6	583.38			3	183.2343922	3
6	4180.7	613	4					4	45.0172678	3
7	4083.9	617.53	5					5	135.0877955	3
8	4328.8	641.41	6					6	104.59988	1
Sheet9

Cell Formulas
Range	Formula
I3:I8	I3	=MIN(SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)))
J3:J8	J3	=MATCH(I3,SQRT(((A3-D$3:D$5)^2)+((B3-E$3:E$5)^2)),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Amazing, yes!! That completely solves my problem, thank you so much :D