Finding the nearest lat and long with an array formula

[samcd87]

I have two sets of co-ordinates, one set shows landmarks and another set shows points where I want to find the nearest landmark to them.

For example;

Landmark	Lat	Long		Points	Lat	Long	Nearest Landmark
Landmark A	53.5168292	-2.2036031		Point 1	53.5171561	-2.2036183	Landmark A
Landmark B	53.5170875	-2.2032919

There is more data than this, but this is an example of the layout.

For the vast majority of the points I have, I've done some sense checks on a map and they are fine. But for a handful, the wrong result appears.

For example above, Point 1 is closer to Landmark B, but Landmark A is given as the result.

The formula in H2 (nearest landmark) is =INDEX($A$1:$A$6476,MATCH(MIN(SQRT(($B$1:$B$6476-F2)^2+($C$1:$C$6476-G2)^2)),SQRT(($B$1:$B$6476-F2)^2+($C$1:$C$6476-G2)^2),0),1)
and this is copied down to all points. I got this formula from here.

Can anybody suggest why this might be happening please - and why it's only happening in some instances?

Any help appreciated!

 

[JamesCanale]

In this case, point 1 looks closer to landmark A to me. Here's my go at it.

MrExcelPlayground12.xlsx
	A	B	C	D	E	F	G	H	I	J
2	Landmark	Lat	Long		Points	Lat	Long	Nearest Landmark
3	Landmark A	53.5168	-2.2036		Point 1	53.517156	-2.2036	Landmark A		0.000327
4	Landmark B	53.5171	-2.2033							0.000334
Sheet26

Cell Formulas
Range	Formula
H3	H3	=INDEX(SORTBY($A$3:$A$4,SQRT((F3-$B$3:$B$4)^2+(G3-$C$3:$C$4)^2)),1)
J3:J4	J3	=SQRT((F3-B3:B4)^2+(G3-C3:C4)^2)
Dynamic array formulas.

 

[samcd87]

Thanks for the reply,

When I use an online calculator to measure the distance from Point 1 to the two landmarks, I get 36.36 metres to Landmark A and 22.89 metres to Landmark B.