Search code examples
excelexcel-formulacoordinatesworksheet-function

Find the nearest set of coordinates in Excel

There are two tables, each with a coordinate pair: longitude and latitude, in decimal degrees (i.e. 37.23222, -121,3433). How do I match table A with its nearest coordinate pair in table B?

Solution

  • You could try filling down this formula from G1 as shown below:

    =LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10)

    For a more accurate formula that takes account of the circular distances try filling down from H1:

    =LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(B$1:B$10-E1))/2)^2+SIN((RADIANS(C$1:C$10-F1))/2)^2*COS(RADIANS(B$1:B$10))*COS(RADIANS(E1))),A$1:A$10)

    enter image description here