SOLVED

Highlighted
New Contributor

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

5 Replies
Highlighted

# Re: IF Statement Help

@areed96 , it's better to create separate table with columns Airline and Gate for the companies which always use same gates.

`=IFNA(INDEX(<second table airlines>, MATCH(<airline>,<second table gates>,0)),"not defined")`

where within <> are your actual (named) ranges.

Highlighted

# Re: IF Statement Help

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

Highlighted
Solution

# Re: 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")`
Highlighted

# Re: IF Statement Help

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

Highlighted

# Re: IF Statement Help

@areed96 , you are welcome