Google Sheets Mathmatical Query with Geospatial Data

Question

I have multiple Locations (Point 1 as shown in the image, cell A2) in google sheets where I'm trying to calculate the closest landmark to from a very large list (simplified for this example cells A6:A17). For each point I want to display the distance to the closest Landmark as well as display the name of that landmark. The issue is I want to do it all in 2 cells (1 cell that displays the location and the second that displays the landmark). I've successfully been able to calculate the distance in a single cell as shown in E2 using:

=min(ARRAYFORMULA(ACOS(COS(RADIANS(90-B2)) *COS(RADIANS(90-B6:B17)) +SIN(RADIANS(90-B2)) *SIN(RADIANS(90-B6:B17)) *COS(RADIANS(C2-C6:C17))) *6371))

However, in cell F2 I want it to display the name of the Landmark (which should be landmark A) I've tried query, lookup, match and index but they don't seem to work well with array formulas. I know I could simply put the distance in column D6:17 and then use query, match, index etc to put the name into F2. However, I have hundreds of points for which need to have their distance calculated to each landmark which would be a lot of unnecessary calculations when I"m only interested in the distance for the closest Landmark.

Picture of Sample Data

Thoughts or suggestions?

player0 player0 · Accepted Answer · 2019-12-19T00:18:40

paste in F2:

=ARRAYFORMULA(VLOOKUP(E2, {ACOS(
 COS(RADIANS(90-B2))*COS(RADIANS(90-B6:B17))+
 SIN(RADIANS(90-B2))*SIN(RADIANS(90-B6:B17))*COS(RADIANS(C2-C6:C17)))*6371, 
 A6:A17}, 2, 0))

Google Sheets Mathmatical Query with Geospatial Data

1 Answers