Help With Lat Long Formulas

[welshraz]

Hello hello,

I have these different formulas which pull through a reference number based on the nearest coordinates:-

=IFERROR(LOOKUP(1,1/FREQUENCY(0,MMULT((CombinedNOI[Lat]:CombinedNOI[Long]-U13:V13)^2,{1;1})),CombinedNOI[ORREFERENCEID]),"")
=IF(U13="","",LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(CombinedNOI[Lat]-U13))/2)^2+SIN((RADIANS(CombinedNOI[Long]-V13))/2)^2*COS(RADIANS(CombinedNOI[Lat]))*COS(RADIANS(U13))),CombinedNOI[ORREFERENCEID]))
=IFERROR(INDEX(CombinedNOI[ORREFERENCEID],MATCH(MIN(SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2)),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),0),1),"")

They work really well, but is there any way of amending them so that they pull through the second and third closest?

 

[rakesh seebaruth]

1. To get the second closest reference: =IFERROR(INDEX(CombinedNOI[ORREFERENCEID],MATCH(LARGE(IF((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2<>MIN((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2)),2),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),0),1),"")
2. To get the third closest reference: =IFERROR(INDEX(CombinedNOI[ORREFERENCEID],MATCH(LARGE(IF((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2<>MIN((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2)),3),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),0),1),"")

 

[welshraz]

1. To get the second closest reference: =IFERROR(INDEX(CombinedNOI[ORREFERENCEID],MATCH(LARGE(IF((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2<>MIN((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2)),2),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),0),1),"")
2. To get the third closest reference: =IFERROR(INDEX(CombinedNOI[ORREFERENCEID],MATCH(LARGE(IF((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2<>MIN((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2)),3),SQRT((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2),0),1),"")

Hmmmm.....I have tested both of these, and whilst they do pull through a different reference as expected, it is not the second or third closest. In all tests the reference that they pulled through is at least 50km away, when in reality they should be within a 5km radius.

 

[rakesh seebaruth]

=INDEX(CombinedNOI[ORREFERENCEID], MATCH(SMALL(IF((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2>0, (CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2), 2), (CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2, 0))

And here's an example for the third closest reference:

=INDEX(CombinedNOI[ORREFERENCEID], MATCH(SMALL(IF((CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2>0, (CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2), 3), (CombinedNOI[Lat]-U13)^2+(CombinedNOI[Long]-V13)^2, 0))

 

[Peter_SSs]

@welshraz
I have removed the 'Mark as solution' tick from post #3 as your comments in that post do not provide a solution to the original question. This is to help future readers.
Note that it is not a 'Mark as solved' or 'I don't require further help' checkmark in our forum.