Creating a table with distance between airports

[swymkyl]

Hi,
I need to create a table giving me the distance between any two airports with the Airport codes listed in the header and the same codes listed in the left column
and using a formula with vlookup to get the longitude abd latitude from a table in another sheet. I want to copy that formula in the whole table using array formula to populate the table. To look like the attached photobelow
And the formula i used was
=ACOS(((SIN(VLOOKUP(A2,KK,4,TRUE)*3.14/180))*SIN((VLOOKUP(A3,KK,4,TRUE)*3.14/180))+(COS(VLOOKUP(A2,KK,4,TRUE)*3.14/180)*COS(VLOOKUP(A3,KK,4,TRUE)*3.14/180)*COS((VLOOKUP(A2,KK,8,TRUE)-VLOOKUP(A3,KK,8,TRUE))*3.14/180))))*180*60/3.14
Where cell A2 the first airport code
And cell A3 the second airport.

 

[Rijnsent]

Hi Swymkyl,
welcome to the forum. What's your question? If you want to drag you formula, you probably want to use $ signs to either lock the column or the row. See e.g. this page on how that works: $ Symbol in Excel | Why use $ (Dollar) Symbol in Excel Formula?
If you have another question, don't hesitate to ask,
Cheers,
Koen

 

[swymkyl]

Hi Koen
Thanks for the reply but the $ sign Firs not D
work Because you are copying in two different
direction so it will work if i am copying the rows only and i have to change the $ every time i go yo the second row or column
But it will be very helpful if i can use an array formula.

 

[Rijnsent]

You don't need an array formula, the $ signs will work... If your purple cell is in A1, for the formula in B2:
=ACOS(((SIN(VLOOKUP($A2,KK,4,TRUE)*3.14/180))*SIN((VLOOKUP(B$1,KK,4,TRUE)*3.14/180))+(COS(VLOOKUP($A2,KK,4,TRUE)*3.14/180)*COS(VLOOKUP(B$1,KK,4,TRUE)*3.14/180)*COS((VLOOKUP($A2,KK,8,TRUE)-VLOOKUP(B$1,KK,8,TRUE))*3.14/180))))*180*60/3.14
And drag that to the right & down.
Good luck,
Koen

 

[swymkyl]

Koen
Thanks it works. You are a life saver?