Help with IFNA formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2083124%22%20slang%3D%22en-US%22%3EHelp%20with%20IFNA%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083124%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20add%20a%20condition%20to%20this%20string%20and%20can%20not%20figure%20out%20how%20(I%20did%20not%20write%20it)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIFNA(IF(BX2%3D%22Y%22%2CBW2%2CIF(AND(BR2%3D%22Y%22%2CBS2%3D%22Y%22%2CBX2%3D%22N%22)%2CBO2%2CIF(AND(BR2%3D%22Y%22%2CBS2%3D%22N%22%2CBX2%3D%22N%22)%2CBO2%2CIF(AND(BR2%3D%22N%22%2CBS2%3D%22N%22%2CBX2%3D%22N%22)%2CBQ2%2CIF(AND(BR2%3D%22N%22%2CBS2%3D%22Y%22%2CBX2%3D%22N%22)%2C%22%22)))))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20add%26nbsp%3B%3C%2FP%3E%3CP%3EBR2%3D%22N%22%2CBS2%3D%22Y%22%2CBX2%3D%22N%22%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20for%20it%20to%20select%20the%20values%20in%20cell%20BQ2%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ETIA!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2083124%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083259%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFNA%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083259%22%20slang%3D%22en-US%22%3ESo%2C%20you%20want%20to%20return%20BQ2%20if%20BR2%3D%22N%22%20and%20BX2%3D%22N%22%20and%20BS2%20%22Y%22%20or%20%22N%22%3F%3CBR%20%2F%3E%3CBR%20%2F%3ETry%3A%3CBR%20%2F%3EIFNA(IF(BX2%3D%22Y%22%2CBW2%2CIF(AND(BR2%3D%22Y%22%2CBS2%3D%22Y%22%2CBX2%3D%22N%22)%2CBO2%2CIF(AND(BR2%3D%22Y%22%2CBS2%3D%22N%22%2CBX2%3D%22N%22)%2CBO2%2CIF(AND(BR2%3D%22N%22%2CBX2%3D%22N%22%2COR(BS2%3D%22N%22%2CBS2%3D%22Y%22))%2CBQ2%2CIF(AND(BR2%3D%22N%22%2CBS2%3D%22Y%22%2CBX2%3D%22N%22)%2C%22%22)))))%2C%22%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2084106%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20IFNA%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2084106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937811%22%20target%3D%22_blank%22%3E%40Jahh2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20lost%20with%20initial%20formula%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(%0A%20%20%20IF(BX2%3D%22Y%22%2CBW2%2C%0A%20%20%20IF(AND(BR2%3D%22Y%22%2CBS2%3D%22Y%22%2CBX2%3D%22N%22)%2CBO2%2C%0A%20%20%20IF(AND(BR2%3D%22Y%22%2CBS2%3D%22N%22%2CBX2%3D%22N%22)%2CBO2%2C%0A%20%20%20IF(AND(BR2%3D%22N%22%2CBS2%3D%22N%22%2CBX2%3D%22N%22)%2CBQ2%2C%0A%20%20%20IF(AND(BR2%3D%22N%22%2CBS2%3D%22Y%22%2CBX2%3D%22N%22)%2C%22%22)))))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20we%20assume%20each%20cell%20could%20have%20Y%20or%20N%20and%20we%20have%20no%20extra%20formulas%20which%20could%20return%20NA%20result%2C%20it%20could%20be%20simplified%20to%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20IF(BX2%3D%22Y%22%2CBW2%2C%0A%20%20%20IF(AND(BR2%3D%22Y%22)%2CBO2%2C%0A%20%20%20IF(BS2%3D%22N%22%2CBQ2%2C%22%22)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20we%20would%20like%20to%20handle%20other%20than%20Y%20and%20N%20values%20it'll%20be%20better%20to%20expand%20the%20formula%20returning%20something%20more%20than%20FALSE%20informative.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need to add a condition to this string and can not figure out how (I did not write it)

 

IFNA(IF(BX2="Y",BW2,IF(AND(BR2="Y",BS2="Y",BX2="N"),BO2,IF(AND(BR2="Y",BS2="N",BX2="N"),BO2,IF(AND(BR2="N",BS2="N",BX2="N"),BQ2,IF(AND(BR2="N",BS2="Y",BX2="N"),""))))),"")

 

I need to add 

BR2="N",BS2="Y",BX2="N" 

and for it to select the values in cell BQ2


TIA!

5 Replies
So, you want to return BQ2 if BR2="N" and BX2="N" and BS2 "Y" or "N"?

Try:
IFNA(IF(BX2="Y",BW2,IF(AND(BR2="Y",BS2="Y",BX2="N"),BO2,IF(AND(BR2="Y",BS2="N",BX2="N"),BO2,IF(AND(BR2="N",BX2="N",OR(BS2="N",BS2="Y")),BQ2,IF(AND(BR2="N",BS2="Y",BX2="N"),""))))),"")

@Jahh2 

I'm lost with initial formula:

=IFNA(
   IF(BX2="Y",BW2,
   IF(AND(BR2="Y",BS2="Y",BX2="N"),BO2,
   IF(AND(BR2="Y",BS2="N",BX2="N"),BO2,
   IF(AND(BR2="N",BS2="N",BX2="N"),BQ2,
   IF(AND(BR2="N",BS2="Y",BX2="N"),""))))),"")

If we assume each cell could have Y or N and we have no extra formulas which could return NA result, it could be simplified to

= IF(BX2="Y",BW2,
   IF(AND(BR2="Y"),BO2,
   IF(BS2="N",BQ2,"")))

If we would like to handle other than Y and N values it'll be better to expand the formula returning something more than FALSE informative.

Thank you@JMB17 

I was trying to figure out where to put the "OR" command - thank you! 

@JMB17 

You are welcome.