Hidden Helpers in Spreadsheets, or How To Find the Closest Airport in Google Sheets
There is so much in technology that is invisible. I’ve written about it here, and here, and here.
We live in a world with a ton of fancy user interfaces (websites) and “no-code” tools and programming languages that make using technology more and more accessible to more people. But the flip side is that this often hides the basic mechanics of how systems, processes, databases, and computers work. How does the internet work - can you explain it in 30 seconds?
And we don’t necessarily need in-depth access to this invisible knowledge all the time. Of course, it can help when something doesn’t work the way you think it should work. It is just like with our relationships with other human beings - the more we know about someone and how they function, the more we understand where they come from and their experiences, the more time we spend together and create together, the better our relationships and the outcomes of our shared work will be.
But that was all a rant to say that sometimes it can be better to do things more simply, so that it is easier to pull them apart and understand where the end result is coming from and how it was achieved. In a recent spreadsheet project I made a choice to not go as complex as possible. Users of the spreadsheet can follow the formula breadcrumbs to see the breakdown of how we got to the end result.
The Situation: A friend asked me to help out with a spreadsheet that could determine the closest airport to a starting location. One note was that we might need to limit the list of airports to a certain few that were available for travel.
How it was accomplished: The heavy lifting of figuring out the distance from one location to another was done by using Amit Agarwal’s Google Maps formulas. Then the sorting and displaying of the airport names and distances was done by using 4 Helper Columns that are grouped and hidden. I also used the new-ish array formulas and lambda formulas, but those are a bit more advanced and the same results could be obtained without using these.
The Details:
Prefer to watch a video of the explanation below? Click here!
1) I use the filter formula to essentially bring the same list of airports that exists on a separate tab (called List of Airports) into the tab I am working in. This could have been accomplished in at least 3 other ways that I know of, this is just a favorite of mine. I didn’t have to, I could have left this Helper column out and just referenced the other tab in the following formulas. But hey, I think it is more helpful all in one place here.
2) I calculate the distance from a starting address (in B3) to the each airport in the list by using the GOOGLEMAPS_DISTANCE formula. I also used a complex array formula so that I would only have to write it once and it would expand down the rows. But if you aren’t there yet, you can write just the simple formula (shown second in the image below) and drag-it-down or copy-and-paste it down next to all the airports in the list. (I don’t like the drag-it-down or copy-and-paste methods because if we didn’t drag it far enough and our list of airports grew, we might miss the calculations for the new airports, but that is for a different rant).
3) Next I ran into a question - I want to ultimately sort the distances from shortest to longest. After doing that, I’ll lookup the name of the airport that is matched to the distance. But what if two airports are both the exact same number of miles apart? Then my lookup would fail, because it would always only choose the first airport it finds that matches the distance. So I created a helper column to make a “unique” value I could sort by combining the distance and the name of the airport. Again, not the only way to do this, but its the one I chose. I again used a complex array formula, but you can easily use the more “simple” second formula from the image below.
4) Finally, I use a helper column with the sort formula to sort the list of distances and airports from shortest to longest distance. Again I used a complex formula that covers a few bases, but you could easily use the “simple” formula shown second in the image below
5) All of that just allows me to do a Lookup. An xlookup to be precise - please note that I will not say you can use the vlookup as a valid alternative. Using Vlookup when Xlookup is available is like signing up for an AOL account in 2023. I look up the miles based on the unique value of the distance and airport name concatenated together, and same thing for the name of the airport. There are many other ways to have accomplished this shortest-to-longest distance list of possible airports, and this is the way I chose for this project.
