IF Statement Help

[areed96]

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!

[#2]

@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. 

[#3]

@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!

[#4 · Accepted 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")

[#5]

@Sergei Baklan 

It works!  You sir a legend thank you very much for your help!

[#6]

@areed96 , you are welcome