Matching closest Lat and Lon

[raven_squire]

Hello, I have two tables. Both have Lat and Lon values in them along with other data.

For each row in table A I need to find the index of table B where the Lat and Lon is the closest.

I have a formula for working out the distance but I don't know how to incorperate it into a match type formula.

Any ideas??

 

[Misca]

Try wrapping the distance formulas in a SUMPRODUCT where you first calculate the distance from every location in your lookup list to you location on the other list and equal that to the MIN of the same formula. This part of the SUMPRODUCT should return only one TRUE. If you add ROW as the second part of the SUMPRODUCT you'll get the matching row number.

Enter with Ctrl + Shift + Enter