excel matching lat and long points

Question

I want to extract a distance value for a number of points from a table of values that have a latitude, longitude and distance point. I have a table that follows a train line along a path and has points every ~20 metres with the kilometre points associated from which i want to extract the distance value.

Lookup Table

I have another table, Reference table, that is positional data of a train every 3-5 minutes along the same path as the lookup table, so the lat and long points wont be the same. I need to search through the lookup table to find the closest lat and long point on the path and extract the associated distance. I am able to find the closest point for the latitude and longitude individually, but because the path is windy, the expected values do not match. I have gone through some of the points that are different to what is expected and in some cases, the distance associated with the latitude is correct, but in others, the distance associated with the longitude is correct.

Reference table

The highlighted values in the reference table are values that did not agree to what they should be, as shown in the lookup table (the same colour refers to the value found in the lookup table). The bold values are points that agreed, which is what i am hoping to achieve for all points. The kilometer points in the Reference table are correct, but i had to manually adjust the equations to use the correct value.

This is the equation that i use to extract the distance. (column J)

=IF(MATCH(MIN(ABS(LookupTable-ReferenceLatitude)),ABS(LookupTable-ReferenceLatitude),0)=MATCH(MIN(ABS(LookupTable-ReferenceLongitude)),ABS(LookupTable-ReferenceLongitude),0), TRUE:extract Distance, FALSE: ??? [both points should agree] )

currently i manually adjust the FALSE condition to extract the distance from the lat or long value as required.

My question is, is there a way to ensure both the latitude and longitude are matched to get the correct distance? and what should be used in the FALSE case for the calculation?

Answer 1

I fave found a solution, quite a complicated one really. First the reasoning, the correct points can be found by finding the minimum distance between your reference points and the lookup points. The minimum distance between the two points will then give you the desired lat, log and associated distance.

I found the spherical law of cosines equation (the least complex one) that allows you to calculate the distance between two points. You can then use MATCH() to find the row of your point and then INDEX() to extract the distance value.

Here is the MATCH() equation

=MATCH(MIN(ABS(2*ATAN2(SQRT(1-(SIN(RADIANS((refLat-lookupLatArray)/2))*SIN(RADIANS((refLat-lookupLatArray)/2))+COS(RADIANS(refLat))*COS(RADIANS(lookupLatArray))*SIN(RADIANS((refLong-lookupLonArray)/2))*SIN(RADIANS((refLong-lookupLonArray)/2)))),SQRT(SIN(RADIANS((refLat-lookupLatArray)/2))*SIN(RADIANS((refLat-lookupLatArray)/2))+COS(RADIANS(refLat))*COS(RADIANS(lookupLatArray))*SIN(RADIANS((refLong-lookupLonArray)/2))*SIN(RADIANS((refLong-lookupLonArray)/2))))*6371)),ABS(2*ATAN2(SQRT(1-(SIN(RADIANS((refLat2-lookupLatArray)/2))*SIN(RADIANS((refLat-lookupLatArray)/2))+COS(RADIANS(refLat))*COS(RADIANS(lookupLatArray))*SIN(RADIANS((refLong2-lookupLonArray)/2))*SIN(RADIANS((refLong-lookupLonArray)/2)))),SQRT(SIN(RADIANS((refLat-lookupLatArray/2))*SIN(RADIANS((refLat-lookupLatArray)/2))+COS(RADIANS(refLat))*COS(RADIANS(lookupLatArray))*SIN(RADIANS((refLong-lookupLonArray/2))*SIN(RADIANS((refLong-lookupLonArray)/2))))*6371),0)