Apr 15 2019 09:17 AM
I'm aiming to produce a report on airline arrivals into different gates at an airport, there are thousands of entries so I need to automate this. I have produced a small example below:
AIRLINE | DAY | TIME | AIRPORT | COUNTRY | GATE |
Eurowings | Mon | 07:15 | COLOGNE (BONN) | GERMANY | |
EasyJet | Mon | 07:20 | BELFAST | UNITED KINGDOM |
I know that Eurowings for example always use Gate 1 and EasyJet always use Gate 3, but what formula can I use that shows this?
So far I keep getting the #REF! error for =IF(B5:B1908 = (Eurowings),(GATE 1), ()) and I can't find what I need to change. I was then going to do a similar formula for EasyJet and the other airlines.
Any help is greatly appreciated!
Apr 15 2019 10:12 AM
@areed96 , it's better to create separate table with columns Airline and Gate for the companies which always use same gates.
In your main table you may add formula like
=IFNA(INDEX(<second table airlines>, MATCH(<airline>,<second table gates>,0)),"not defined")
where within <> are your actual (named) ranges.
Apr 16 2019 02:27 AM
@Sergei Baklan Thanks Sergei!
I feel like I'm nearly there but I'm now getting the #NAME? error.
I've created 2 separate columns with airlines and gates as below:
Ryanair | Gate 1 | |
EasyJet | Gate 2 | |
TUI | Gate 3 | |
Jet2 | ||
Thomas Cook | ||
Emirates | ||
Eurowings |
The formula I have used is =IFNA(INDEX(L5:L11, MATCH(EasyJet, N5,0)),"not defined")
(N5 being Gate 1) (L5:L11 being the list of airlines) I know that EasyJet always use Gate 1.
What amendments do I need to make?
Thank you!
Apr 16 2019 08:02 AM
Solution@areed96 , for such sample
the formula is
=IFNA(INDEX($K$3:$K$9, MATCH(B3,$I$3:$I$9,0)),"not defined")
To make it more flexible you may convert your ranges into table (Sheet2)
=IFNA(INDEX(Gates[GATE], MATCH([@AIRLINE],Gates[AIRLINE],0)),"not defined")
or use dynamic ranges (Sheet3), here all cells are to be filled
=IFNA(INDEX($K$3:INDEX($K:$K,COUNTA($K:$K)+ROW($K$2)), MATCH($B3,$I$3:INDEX($I:$I,COUNTA($I:$I)+ROW($I$2)),0)),"not defined")
Apr 17 2019 03:57 AM
It works! You sir a legend thank you very much for your help!
Apr 16 2019 08:02 AM
Solution@areed96 , for such sample
the formula is
=IFNA(INDEX($K$3:$K$9, MATCH(B3,$I$3:$I$9,0)),"not defined")
To make it more flexible you may convert your ranges into table (Sheet2)
=IFNA(INDEX(Gates[GATE], MATCH([@AIRLINE],Gates[AIRLINE],0)),"not defined")
or use dynamic ranges (Sheet3), here all cells are to be filled
=IFNA(INDEX($K$3:INDEX($K:$K,COUNTA($K:$K)+ROW($K$2)), MATCH($B3,$I$3:INDEX($I:$I,COUNTA($I:$I)+ROW($I$2)),0)),"not defined")