SOLVED
Home

IF Statement Help

%3CLINGO-SUB%20id%3D%22lingo-sub-455560%22%20slang%3D%22en-US%22%3EIF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455560%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20aiming%20to%20produce%20a%20report%20on%20airline%20arrivals%20into%20different%20gates%20at%20an%20airport%2C%20there%20are%20thousands%20of%20entries%20so%20I%20need%20to%20automate%20this.%26nbsp%3B%20I%20have%20produced%20a%20small%20example%20below%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EAIRLINE%3C%2FTD%3E%3CTD%3EDAY%3C%2FTD%3E%3CTD%3ETIME%3C%2FTD%3E%3CTD%3EAIRPORT%3C%2FTD%3E%3CTD%3ECOUNTRY%3C%2FTD%3E%3CTD%3EGATE%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EEurowings%3C%2FTD%3E%3CTD%3EMon%3C%2FTD%3E%3CTD%3E07%3A15%3C%2FTD%3E%3CTD%3ECOLOGNE%20(BONN)%3C%2FTD%3E%3CTD%3EGERMANY%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEasyJet%3C%2FTD%3E%3CTD%3EMon%3C%2FTD%3E%3CTD%3E07%3A20%3C%2FTD%3E%3CTD%3EBELFAST%3C%2FTD%3E%3CTD%3EUNITED%20KINGDOM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20Eurowings%20for%20example%20always%20use%20Gate%201%20and%20EasyJet%20always%20use%20Gate%203%2C%20but%20what%20formula%20can%20I%20use%20that%20shows%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20I%20keep%20getting%20the%20%23REF!%20error%20for%26nbsp%3B%3DIF(B5%3AB1908%20%3D%20(Eurowings)%2C(GATE%201)%2C%20())%20and%20I%20can't%20find%20what%20I%20need%20to%20change.%26nbsp%3B%20I%20was%20then%20going%20to%20do%20a%20similar%20formula%20for%20EasyJet%20and%20the%20other%20airlines.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-455560%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-455864%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-455864%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321262%22%20target%3D%22_blank%22%3E%40areed96%3C%2FA%3E%26nbsp%3B%2C%20it's%20better%20to%20create%20separate%20table%20with%20columns%20Airline%20and%20Gate%20for%20the%20companies%20which%20always%20use%20same%20gates.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20main%20table%20you%20may%20add%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(INDEX(%26lt%3Bsecond%20table%20airlines%26gt%3B%2C%20MATCH(%26lt%3Bairline%26gt%3B%2C%26lt%3Bsecond%20table%20gates%26gt%3B%2C0))%2C%22not%20defined%22)%3C%2FPRE%3E%0A%3CP%3Ewhere%20within%20%26lt%3B%26gt%3B%20are%20your%20actual%20(named)%20ranges.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-459338%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20Sergei!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20feel%20like%20I'm%20nearly%20there%20but%20I'm%20now%20getting%20the%20%23NAME%3F%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20created%202%20separate%20columns%20with%20airlines%20and%20gates%20as%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ERyanair%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EGate%201%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEasyJet%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EGate%202%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETUI%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EGate%203%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJet2%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThomas%20Cook%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmirates%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEurowings%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20have%20used%20is%26nbsp%3B%3DIFNA(INDEX(L5%3AL11%2C%20MATCH(EasyJet%2C%20N5%2C0))%2C%22not%20defined%22)%3C%2FP%3E%3CP%3E(N5%20being%20Gate%201)%20(L5%3AL11%20being%20the%20list%20of%20airlines)%26nbsp%3B%20I%20know%20that%20EasyJet%20always%20use%20Gate%201.%3C%2FP%3E%3CP%3EWhat%20amendments%20do%20I%20need%20to%20make%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-461196%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-461196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321262%22%20target%3D%22_blank%22%3E%40areed96%3C%2FA%3E%26nbsp%3B%2C%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20762px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109098i758BDA61F16F2C9D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethe%20formula%20is%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(INDEX(%24K%243%3A%24K%249%2C%20MATCH(B3%2C%24I%243%3A%24I%249%2C0))%2C%22not%20defined%22)%3C%2FPRE%3E%0A%3CP%3ETo%20make%20it%20more%20flexible%20you%20may%20convert%20your%20ranges%20into%20table%20(Sheet2)%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(INDEX(Gates%5BGATE%5D%2C%20MATCH(%5B%40AIRLINE%5D%2CGates%5BAIRLINE%5D%2C0))%2C%22not%20defined%22)%3C%2FPRE%3E%0A%3CP%3Eor%20use%20dynamic%20ranges%20(Sheet3)%2C%20here%20all%20cells%20are%20to%20be%20filled%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(INDEX(%24K%243%3AINDEX(%24K%3A%24K%2CCOUNTA(%24K%3A%24K)%2BROW(%24K%242))%2C%20MATCH(%24B3%2C%24I%243%3AINDEX(%24I%3A%24I%2CCOUNTA(%24I%3A%24I)%2BROW(%24I%242))%2C0))%2C%22not%20defined%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-465521%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-465521%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works!%26nbsp%3B%20You%20sir%20a%20legend%20thank%20you%20very%20much%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469023%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469023%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321262%22%20target%3D%22_blank%22%3E%40areed96%3C%2FA%3E%20%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
areed96
New Contributor

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: 

 

AIRLINEDAYTIMEAIRPORTCOUNTRYGATE
EurowingsMon07:15COLOGNE (BONN)GERMANY 
EasyJetMon07:20BELFASTUNITED 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

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

 

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

Solution

@areed96 , for such sample

image.png

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")

@Sergei Baklan 

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

@areed96 , you are welcome