Challenge: "Closest" location lookup using Longitude / Latitude

[bnels]

I have a list of a number of locations and their corresponding Longitude and Latitude.

I need to be able to enter current Longitude and Latitude from a GPS unit into a form and give back the closest location from the Excel table (nearest match by distance for both Long and Lat)

One of the tricks gets to be if for example the desired longitudes are similar for multiple locations but the latitudes are different.

However I have no idea where to start. I was thinking of trying to calculate actual distance from desired location to each of the locations in the list (not even really sure how to convert long/lat to feet) but that didn't seem as efficient.

Is there a way to do this with Index/Match or an array formula or something?

Does anyone have any ideas?

 

[cyrilbrd]

Hi, there would be a cool (ahem) way of doing this but may I ask if you already have a list of possible locations with their respective latitude and longitude?
We could then use a formula to compute the distance and indeed an index system to return a single or multiples answers (in case of a tie)

 

[bnels]

Yes, I have a large table with....

Loc'n_ID Long Lat

 

[cyrilbrd]

Ok noted, do you want only one (1) result of several result, example all possible closest locations within ±50km...

 

[bnels]

Looking for single closest location...

I did find this formula to calculate distance between pair of long/lat coords in feet which got me close. Putting this formula in an adjacent column and filling down. How do I pick the closest coordinates in table (ie. lowest calculated formula value)

entry long lat: B2=lat1, c2=long1
table data b5=lat2, c5=long2

=(ACOS(COS(RADIANS(90-$B$2))*COS(RADIANS(90-B5))+SIN(RADIANS(90-$B$2))*SIN(RADIANS(90-B5))*COS(RADIANS($C$2-C5)))*3958.756)*5280

 

[cyrilbrd]

given as follows:

Excel 2013
	A	B	C	D	E	F	G
1	Loc_ID	longitude	latitude	distance			6371
2	03N	11.222	169.852	11334.8
3	07FA	25.325393	-80.274775	16286.79
4	07MT	48.4211069	-106.5277039	19029.28
5	0WI8	43.1388947	-88.4723206	17191.8
6	18AZ	33.8180947	-111.8979242	19794.49
7	1AZ0	33.111944	-112.269166	19833.16
8	20GA	33.7565556	-84.8850625	16790.79
9	2XS8	29.229405	-99.823947	18446.13
10	3N0	5.63167	168.125	11038.09
11	5A8	59.2826	-158.618	14717.25
12	66CA	35.607748	-121.110192	19206.17
13	7FA1	24.6487544	-81.5798083	16432.3
list

then:

Excel 2013
	A	B	C	D
1			longitude	latitude
2		search	33.81665	66.11817
3	results	OATN	32.605278	65.864167
result

adjust range to fit your model

 

[cyrilbrd]

For whatever reason the formulae are not included...
In sheet 'List'
Name range RadiusEarth refers to cell G1
Formula in D2 is =RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS([@latitude])-RADIANS(lat_2))/2)^2)+COS(RADIANS([@latitude]))*COS(RADIANS(lat_2))*(SIN((RADIANS([@longitude])-RADIANS(long_2))/2)^2)))))

In sheet 'result'
Name range long_2 is cell C2
Name range lat_2 is cell D2
Formula in B3 is =INDEX(Table1[Loc_ID],MATCH(SMALL(Table1[distance],ROWS(result!B$3:B3)),Table1[distance],0))
Formula in C3 is =INDEX(Table1[longitude],MATCH(result!B3,Table1[Loc_ID],0))
Formula in D3 is =INDEX(Table1[latitude],MATCH(result!B3,Table1[Loc_ID],0))

 

[bnels]

Couldn't quite get your formulas to work in my table but you gave me the INDEX formula I needed to add to my distance formula to pick the closest from the list so I got it to work - much appreciated!

My solution using your INDEX formula from B3 above...

INPUT LONG/LAT (FIND ID & ACCURACY):
CLOSEST_ID Long1 Lat1 Accuracy (ft)
9+02 -88.### 40.### 2.5 <<< Used "MIN" formula to show distance to closest ID

CLOSEST_ID=INDEX(Table1[ID],MATCH(SMALL(Table1[distance],ROWS(A$2:A2)),Table1[distance],0))


CALC TABLE:
ID long2 lat2 distance
7+56 -88.##### 40.##### 193.26352777

DISTANCE=(ACOS(COS(RADIANS(90-$B$2))*COS(RADIANS(90-B5))+SIN(RADIANS(90-$B$2))*SIN(RADIANS(90-B5))*COS(RADIANS($C$2-C5)))*3958.756)*5280

 

[cyrilbrd]

Couldn't quite get your formulas to work in my table but you gave me the INDEX formula I needed to add to my distance formula to pick the closest from the list so I got it to work - much appreciated!

My solution using your INDEX formula from B3 above...

INPUT LONG/LAT (FIND ID & ACCURACY):
CLOSEST_ID Long1 Lat1 Accuracy (ft)
9+02 -88.### 40.### 2.5 <<< Used "MIN" formula to show distance to closest ID

CLOSEST_ID=INDEX(Table1[ID],MATCH(SMALL(Table1[distance],ROWS(A$2:A2)),Table1[distance],0))


CALC TABLE:
ID long2 lat2 distance
7+56 -88.##### 40.##### 193.26352777

DISTANCE=(ACOS(COS(RADIANS(90-$B$2))*COS(RADIANS(90-B5))+SIN(RADIANS(90-$B$2))*SIN(RADIANS(90-B5))*COS(RADIANS($C$2-C5)))*3958.756)*5280

Glad you got it sorted out.
Formula given would give distance in km
Min would work as well
Solution in B3 would have given second closest distance if dragged down (and do on).