SOLVED

Multiple Condition IF Statement

%3CLINGO-SUB%20id%3D%22lingo-sub-2335079%22%20slang%3D%22en-US%22%3EMultiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335079%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20dataset%20similar%20to%20the%20below%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EMech%20Complete%3F%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EScaffold%20Erected%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EInsulation%20Removed%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EWOL%20Raised%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EGood%20to%20Go%3F%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EN%2FA%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EYes%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20column%20E%20('Good%20to%20Go')%20to%20perform%20the%20following%20logic%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20cells%20in%20columns%20A%20-%20D%20contain%20%22Yes%22%20or%20%22N%2FA%22%20formulate%20to%20%22Yes%22%3C%2FP%3E%3CP%3EIf%20cells%20in%20columns%20A%20-%20D%20contain%20a%20single%20'Blank%20Cell'%20formulate%20to%20'Blank'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EElliot%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2335079%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-2335116%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20499px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279071i919D7593D3CBAB34%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335225%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335225%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%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%20Baklan%2C%3C%2FP%3E%3CP%3EThanks%20for%20your%20quick%20reply.%20I%20have%20tried%20this%20formula%20in%20my%20spreadsheet%20and%20it%20is%20not%20working%2C%20when%20I%20change%20cells%20to%20blank%20it%20does%20not%20change%20the%20'Good%20to%20Go'%20column%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20copy%20%26amp%3B%20pasting%20my%20data%20over%20to%20your%20spreadsheet%20and%20it%20seems%20to%20work%20perfectly.%20Columns%20A%20-%20D%20are%20all%20populated%20by%20IF%20formulas%2C%20I'm%20not%20sure%20if%20this%20has%20anything%20to%20do%20with%20it%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20spreadsheet%20as%20this%20may%20give%20you%20an%20idea%20as%20to%20why%20it%20is%20not%20working%20(Data%20is%20actually%20located%20in%20columns%20AF%3AAI)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EElliot%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335244%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20that's%20since%20you%20could%20have%20not%20blank%20cells%2C%20but%20cells%20with%20empty%20string%20%22%22%20returned%20by%20formulas.%20Empty%20string%20doesn't%20mean%20the%20cell%20is%20blank%2C%20it%20only%20looks%20like%20like%20blank.%3C%2FP%3E%0A%3CP%3EI'll%20modify%20the%20formula%20in%20short%20while%2C%20have%20to%20shift%20on%20another%20task%20shortly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335366%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20change%20the%20check%20on%20empty%20string%20the%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNA(XMATCH(%22%22%2CAF3%3AAI3))%2C%20%22Yes%22%2C%20%22N%2FA%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20attached%20file%20I%20changed%20K3%20to%20test.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335390%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335390%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%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20works%20perfect%2C%20thank%20you%20very%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20good%20weekend.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EElliot%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335397%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EElliot%2C%20glad%20to%20help.%20Have%20a%20nice%20weekend%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2354622%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Condition%20IF%20Statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2354622%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20now%20added%20a%20new%20column%20which%20has%20to%20come%20into%20the%20equation%20and%20was%20wondering%20if%20you%20could%20give%20me%20a%20helping%20hand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20new%20column%20called%20'On%20Hold'%20has%20been%20added%20to%20the%20end%20of%20the%20table%20(let's%20say%20column%20Z).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20fields%20within%20this%20column%20state%20'On%20Hold'%20I%20need%20the%20previous%20'Good%20to%20Go'%20column%20to%20state%20%22N%2FA%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20is%20all%20clear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EElliot%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All,

 

I have a dataset similar to the below;

 

Mech Complete?Scaffold ErectedInsulation RemovedWOL RaisedGood to Go?
YesN/AYes  
N/AYesYesN/A 
N/AN/AYesN/A 
 N/AYes  

 

I need a formula column E ('Good to Go') to perform the following logic;

 

If cells in columns A - D contain "Yes" or "N/A" formulate to "Yes"

If cells in columns A - D contain a single 'Blank Cell' formulate to 'Blank'

 

Any help will be much appreciated!

 

Elliot 

10 Replies

@Serdet 

Perhaps

image.png

@Sergei Baklan  

 

Hi Sergei Baklan,

Thanks for your quick reply. I have tried this formula in my spreadsheet and it is not working, when I change cells to blank it does not change the 'Good to Go' column value.

 

I have tried copy & pasting my data over to your spreadsheet and it seems to work perfectly. Columns A - D are all populated by IF formulas, I'm not sure if this has anything to do with it not working.

 

Please see the attached spreadsheet as this may give you an idea as to why it is not working (Data is actually located in columns AF:AI)

 

Kind regards,

 

Elliot 

@Serdet 

I guess that's since you could have not blank cells, but cells with empty string "" returned by formulas. Empty string doesn't mean the cell is blank, it only looks like like blank.

I'll modify the formula in short while, have to shift on another task shortly.

best response confirmed by Serdet (Occasional Contributor)
Solution

@Serdet 

To change the check on empty string the formula could be

=IF(ISNA(XMATCH("",AF3:AI3)), "Yes", "N/A")

In attached file I changed K3 to test.

@Sergei Baklan

 

That works perfect, thank you very much!

 

Have a good weekend.

 

Elliot  

@Serdet 

Elliot, glad to help. Have a nice weekend too.

@Sergei Baklan 

 

Hi Sergei,

 

I have now added a new column which has to come into the equation and was wondering if you could give me a helping hand.

 

A new column called 'On Hold' has been added to the end of the table (let's say column Z).

 

If fields within this column state 'On Hold' I need the previous 'Good to Go' column to state "N/A".

 

I hope this is all clear.

 

Cheers

 

Elliot 

@Serdet 

You may wrap the formula with one more condition

=IF(Z3="On Hold", "N/A",  IF(ISNA(XMATCH("",AF3:AI3)), "Yes", "N/A"))
Hi Sergei,

That works great. Thank you.

Elliot Serdet

@Serdet , you are welcome