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")
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!
areed96 , you are welcome