Hello All,

I am working on two spreadsheets, one we shall call Target Sheet and the other Reference Sheet.

On the target sheet, I have a set of 200 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site.

On the reference sheet, I have a set of 900 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site. Note that of the 900, 200 are the same from target sheet, other 700 are different sites.

Using the spherical law of cosines, I can calculate the distance between a set of coordinates on target sheet agianst a set of coordinates on reference sheet. This allows me to see the distance from one geographic site, to the other. This was easy. The formula I use is as follows:

=acos(sin(lat1*pi()/180)*sin(lat2*pi()/180)+cos(lat1*pi()/180)*cos(lat2*pi()/180)*cos(lon2*pi()/180-lon1*pi()/180)*R

lat1 = coordinates from Target sheet in decimal degrees

lat2 = coordinates from reference sheet in decimal degrees

lon1 = coordinates from Target sheet in decimal degrees

lon2 = coordinates from reference sheet in decimal degrees

R = radius of the earth in kilometres

ULTIMATE GOAL - On the target sheet, in one cell, I want to calculate the distance between ONE set of coordinates from the target sheet AGIANST all 900 coordinates on the reference sheet while ONLY returning the value which is less than or equal to 50km, does not equal zero and is the MINIMUM distance.

I am certain this involves arrays which i know how to use (Basic ones) however when i try to do this I cannot get this and the flow of the syntax is very tricky for me.

I am not sure if anyone can help but it would be very appreciated. Thank you if you need clarification, or examples I can provide. Thanks

I am working on two spreadsheets, one we shall call Target Sheet and the other Reference Sheet.

On the target sheet, I have a set of 200 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site.

On the reference sheet, I have a set of 900 geographic coordinates longitude and latitude (in decimal degrees). Each pair of coordinates coresponds to a geographic site. Note that of the 900, 200 are the same from target sheet, other 700 are different sites.

Using the spherical law of cosines, I can calculate the distance between a set of coordinates on target sheet agianst a set of coordinates on reference sheet. This allows me to see the distance from one geographic site, to the other. This was easy. The formula I use is as follows:

=acos(sin(lat1*pi()/180)*sin(lat2*pi()/180)+cos(lat1*pi()/180)*cos(lat2*pi()/180)*cos(lon2*pi()/180-lon1*pi()/180)*R

lat1 = coordinates from Target sheet in decimal degrees

lat2 = coordinates from reference sheet in decimal degrees

lon1 = coordinates from Target sheet in decimal degrees

lon2 = coordinates from reference sheet in decimal degrees

R = radius of the earth in kilometres

ULTIMATE GOAL - On the target sheet, in one cell, I want to calculate the distance between ONE set of coordinates from the target sheet AGIANST all 900 coordinates on the reference sheet while ONLY returning the value which is less than or equal to 50km, does not equal zero and is the MINIMUM distance.

I am certain this involves arrays which i know how to use (Basic ones) however when i try to do this I cannot get this and the flow of the syntax is very tricky for me.

I am not sure if anyone can help but it would be very appreciated. Thank you if you need clarification, or examples I can provide. Thanks

## Comment