# How in Excel in a quick way to show the Distance Between Two Airports

In this Excel tutorial I will show how we can use Excel and calculate the **distance between the two airports in the world**. In order to calculate the distance, we need the **latitude and longitude** of the particular airport/city. In fact, two parameters are the coordinates of a particular airport. So remember these terms below.

**Latitude****Longitude**

Based on coordinates of latitude and longitude using the Haversine formula, we can calculate the air distance between the two airports. In the picture below, notice the elements of the Haversine formula. In the text below, you will see how this formula looks in Excel.

# Calculation of distance between two cities

In the picture below, note the dropdown menu layout from the starting point to the destination. In the cells 'A2', 'B2', 'C2', 'A5', 'B5' and 'C5' there are drop-down menus. In cells 'B2', 'B5' and 'C2', 'C5' there are dependent drop-down menus because they depend on the previous data in front of the cell. This means if you select the country 'Croatia' in the cell 'B2'/'B5' you will be able to choose only the cities that are in Croatia. Also, in the 'C2'/'C5' cell, you can only select the names of the airports in the city concerned (of course, if there are more than one).

In the eighth row, there are formulas that automatically return results for the conditions set in the previously mentioned cells.

## Worksheet "WorldCountries"

This worksheet contains a list of countries in the world with its codes, labels continents and web links for details on the country (state). This worksheet is not important and you can delete it if you want.

# Creating drop-down menus for the country, city, and name of the airport from the starting point

On the worksheet "Distance Between Airports" there are the following:

**The drop-down menu 'from Country'**

As you can see in the picture below, in cell 'A2' is a drop-down menu where you can choose one of 237 countries (states) that are found in the database.

Set 'Data Validation' in the 'A2' cell according to the instructions from the link, and in the "Source field" set the formula

=helper!$A$2:$A$238

**Dependent dropdown menu 'from City'**

As you can see in the picture below, in the 'B2' cell there is a drop down menu where you can select one of the 6679 cities with one or more airports located in the database.

Set 'Data Validation' in cell 'B2' according to the instructions from the link, and in the "Source field", set the formula below. (Note! Named formulas are found in the text of this tutorial in the chapter Name Manager).

=OFFSET(StartCell,1,MATCH($A2,Countries,0)-1,COUNTA(OFFSET(StartCell,,MATCH($A2,Countries,0)-1,10000,1))-1,1)

**Dependent drop-down menu 'from Airport Name'**

As you can see in the picture below, in the 'C2' cell there is a drop down menu where you can choose one of the airports for the previously selected city, of course if the city has only one airport then it will only be on the list.

Set 'Data Validation' in the 'C2' cell according to the instructions from the link, and in the "Source field", set the formula below. This drop-down menu pulls items from the 'J' help column (explanation in the chapter 'help columns' below the tutorial).

=$J$2:$J$7

## Creating drop-down menus for a destination: country, city, and airport name

The previously described methods for creating Data Validation also apply to destination drop-down menus.

**The drop-down menu 'to Country'**

As you can see in the picture below, the cell 'A5' is a drop-down menu where you can choose one of 237 countries (states) that are found in the database.

To set up 'Data Validation' in cell 'A5', follow the instructions in the link and place the formula in the 'Source field'

=helper!$A$2:$A$238

**Depending on the drop down menu 'to City'**

As you can see in the picture below, in the 'B5' cell there is a drop-down menu where you can select one of the 6679 cities with one or more airports located in the database.

Set 'Data Validation' in the 'B5' cell according to the instructions from the link, and in the "Source field", set the formula below. (Note! Named formulas are found in the text of this tutorial in the chapter Name Manager).

=OFFSET(StartCell,1,MATCH($A2,Countries,0)-1,COUNTA(OFFSET(StartCell,,MATCH($A2,Countries,0)-1,10000,1))-1,1)

**Dependent drop down menu 'to Airport Name'**

As you can see in the picture below, in the 'C5' cell there is a drop down menu where you can choose one of the airports for the previously selected city, of course if the city has only one airport then it will only be on the list.

Set 'Data Validation' in the 'C2' cell according to the instructions from the link, and in the "Source field", set the formula below. This drop-down menu pulls items from the auxiliary column 'L' (See the chapter 'helper columns' in below tutorials).

=$L$2:$L$7

**Helper columns for creating a dependent drop-down list of airport names**

In the picture below, note the two auxiliary columns in the worksheet "Distance Between Airports". These two columns contain a list of all airport names in the city you chose in the 'B2'/'C5' and returns the list of all airports belonging to the relevant city.

The ARRAY formula in the 'J2' cell is: (This formula returns a list of all the airport names for the city that is set in the 'B2' cell.

=IFERROR(INDEX(tbl,SMALL(IF(COUNTIF($A$2,AirportsDatabase2017!$B$2:$B$7185)*COUNTIF($B$2,AirportsDatabase2017!$C$2:$C$7185),ROW(tbl)-MIN(ROW(tbl))+1),ROW(C1)),COLUMN(C1)),"")

The ARRAY formula in cell 'L2' is: (This formula returns a list of all city airport names that are set in cell 'B5'.

=IFERROR(INDEX(tbl,SMALL(IF(COUNTIF($A$5,AirportsDatabase2017!$B$2:$B$7185)*COUNTIF($B$5,AirportsDatabase2017!$C$2:$C$7185),ROW(tbl)-MIN(ROW(tbl))+1),ROW(C1)),COLUMN(C1)),"")

**Naming Formulas in the Name Manager**

Name Manager is located on the Formulas Tab Ribbon. It contains formulas that are nested in the OFFSET formula. There is also a named data range "**tbl**" that serves as a database for the VLOOKUP formula.

**Named formulas in the Name Manager**

Name: **Countries**

Refers to: =OFFSET(StartCell,,,1,COUNTA(OFFSET(StartCell,,,,100)))

Name: **StartCell**

Refers to: =helper!$B$1

Name: **tbl**

Refers to: =AirportsDatabase2017!$B$2:$L$7185

# How to Automatically Display IATA International Codes for a specific airport

In the figure below in the eighth row there are formulas that return the results for the set conditions in the second and fifth row (A:C). Notice that in the 'A8' and 'B8' cells, the returned result is the **IATA international cod**e that is unique to each aerodrome. The 'C8' result is the **distance between the two airports**. In D8:G8 cells there are latitude and longitude for the respective cities/airports.

**On the worksheet "Distance Between Airports" formula are as follows:**

The formula in the cell 'A8' is: (This formula returns the result from the worksheet 'AirDatabase2017' from the second column for the condition of the cell 'C2').

=IFERROR(VLOOKUP($C$2,AirportsDatabase2017!$D$2:$E$7185,2,FALSE),"")

The formula in the cell 'B8' is: (This formula returns the result from the worksheet 'AirDatabase2017' from the second column for the condition of the cell 'C5').

=IFERROR(VLOOKUP($C$5,AirportsDatabase2017!$D$2:$E$7185,2,FALSE),"")

The formula in the cell 'C8' is: (This formula uses the Haversine formula for calculating the distance between the two airports, the formula is based on latitude and longitude in columns 'D8:E8' and 'F8:G8').

=IFERROR(ACOS(COS(RADIANS(90-D8))*COS(RADIANS(90-F8))+SIN(RADIANS(90-D8))*SIN(RADIANS(90-F8))*COS(RADIANS(E8-G8)))*6371,"")

The formula in the cell 'D8' is: (This formula returns the latitude from the 'AirDatabase2017' sheet for the condition of the cell 'A8').

=IFERROR(VLOOKUP($A8,AirportsDatabase2017!$E$2:$H$7185,3,0),"")

The formula in the cell 'E8' is: (This formula returns the latitude from the worksheet 'AirDatabase2017' for cell condition 'A8).

=IFERROR(VLOOKUP($A8,AirportsDatabase2017!$E$2:$H$7185,4,0),"")

The formula in the cell 'F8' is: (This formula returns the latitude from the 'AirDatabase2017' sheet for the condition of the cell 'B8').

=IFERROR(VLOOKUP($B8,AirportsDatabase2017!$E$2:$H$7185,3,0),"")

The formula in the cell 'G8' is: (This formula returns the latitude from the 'AirDatabase2017' worksheet for the 'B8' condition).

=IFERROR(VLOOKUP($B8,AirportsDatabase2017!$E$2:$H$7185,4,0),"")

**Worksheet "Airports DataBase"**

This worksheet contains all the information needed to calculate the distance between the two airports. This database is from 2017 and the source is "https://ourairports.com/data/#excel"

**Worksheet "Helper"**

This worksheet contains unique lists of all countries of the world (states) and associated cities that have an airport. All lists are sorted alphabetically. In the column 'A' there is a list of all countries/states, and in the columns 'B1:ID1' there is the name of the country/state and below the respective cities. This "helper" worksheet is a data source for the second dependent drop-down menu located in the cells 'B2' and 'B5' in the worksheet '**Distance Between Airports**'.

### Automatically remove dependent drop-down lists with a change of data

As you know when changing data in drop-down menus when you want to change the starting country or city, it is desirable that the drop-down menus are removed, so you have a visual warning that you need to select a new item from the list. For this purpose, you can use the VBA macro below.

Copy this VBA macro to the 'Sheet Module' of your VBE for Excel

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2")) Is Nothing Then

Sheets("Distance Between Airports").Range("B2:C2").Value = ""

End If

If Not Intersect(Target, Range("B2")) Is Nothing Then

Sheets("Distance Between Airports").Range("C2").Value = ""

End If

If Not Intersect(Target, Range("A5")) Is Nothing Then

Sheets("Distance Between Airports").Range("B5:C5").Value = ""

End If

If Not Intersect(Target, Range("B5")) Is Nothing Then

Sheets("Distance Between Airports").Range("C5").Value = ""

End If

End Sub

To make this macro functional, save your workbook in ***.xls** or ***.xlsm** or ***.xlsb** format

Please note that you must correctly set absolute and relative addresses in formulas, especially if you copy the formula.

Finally, in the attachment of this tutorial, you have the **DOWNLOAD file** option on which I did this tutorial.