Forum Discussion
IF Statement Help
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")
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")
It works! You sir a legend thank you very much for your help!
