Forum Discussion
IF Statement Help
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!
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")
5 Replies
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.
SergeiBaklan 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!
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")
