compare all distances and match with the closest item

[kamiltuncer]

Hello everybody

Consider I have something like attached.

What I need to do is; compare distances between A1-B1, A1-B2, A1-B3... etc. and match A1 with the closest one (in this case it is B1)

I have X-Y coordinates for all items here.

I attached a screenshot of a sample excel file to make it more explanatory.

I did this with calculating differences between X & Y coordinates and apply pythagoras theorem but the problem is I have to write very long formulas in each cell.

I believe very small VBA code can solve this but Im zero with VBA.

Can anybody help about this issue please

Dropbox - Untitled.png

Dropbox - Untitled2.png

Thanks

 

[AlanY]

try this with a slim down version of your data

Excel 2012
	A	B	C	D	E	F	G	H
1		X	Y				X	Y
2	A1	37	8	B7		B1	16	45
3	A2	14	40	B4		B2	6	30
4	A3	40	34	B5		B3	14	26
5	A4	47	10	B7		B4	14	42
6	A5	36	27	B10		B5	43	43
7	A6	48	49	B8		B6	22	48
8	A7	14	34	B10		B7	23	5
9	A8	44	13	B7		B8	47	47
10	A9	35	37	B5		B9	50	44
11	A10	25	28	B10		B10	19	29
Sheet1

Cell Formulas
Range	Formula
D2	{=INDEX($F$2:$F$11,MATCH(MIN((($B2-$G$2:$G$11)^2+($C2-$H$2:$H$11)^2)^0.5),(($B2-$G$2:$G$11)^2+($C2-$H$2:$H$11)^2)^0.5,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kamiltuncer]

Well Alan Thank you very much. I must immediately start understanding the logic of INDEX formula

cheers

 

[AlanY]

you're welcome