Forum Discussion

Nandhu19940's avatar
Nandhu19940
Copper Contributor
Apr 01, 2020
Solved

IF formula implementation with multiple logic

Table data: A                  B                  C                         D COUNT COM-RESULT LOOK-VAL1 LKVALUE-2 1 FALSE CEM -- 1 FALSE NONCAT -- 1 FALSE INFORMATION -- ...
excel
Formulas and Functions
  • Branislav1984's avatar
    Branislav1984
    Apr 01, 2020
    Hi Nandhu19940

    Try plugging this in:

    =IF(AND(IF(AND(A2=1,B2=FALSE,D2="--"),"AN","-")="AN",IF(OR(C2="CEM",C2="NONCAT",C2="INFORMATION",C2="FINANCE",C2="CR",C2="PENSION",C2="UINV",C2="CTRLM",C2="JIPE",C2="CSARC"),"AN","-")="AN"),"AN","-")

    Just replace "AN" with "Required action"
    BR,
Branislav1984's avatar
Branislav1984
Brass Contributor
Apr 01, 2020
Hi Nandhu19940

Try plugging this in:

=IF(AND(IF(AND(A2=1,B2=FALSE,D2="--"),"AN","-")="AN",IF(OR(C2="CEM",C2="NONCAT",C2="INFORMATION",C2="FINANCE",C2="CR",C2="PENSION",C2="UINV",C2="CTRLM",C2="JIPE",C2="CSARC"),"AN","-")="AN"),"AN","-")

Just replace "AN" with "Required action"
BR,
Nandhu19940's avatar
Nandhu19940
Copper Contributor
Apr 01, 2020

Branislav1984 

Dear Bran,

Your formula works perfectly and kindly explain me the concept behind that one.

 

  • Branislav1984's avatar
    Branislav1984
    Brass Contributor
    Apr 01, 2020

    Hi Nandhu19940 

     

    I basically did two tests and merged them into one....so,

     

    =IF(AND(A2=1,B2=FALSE,D2="--"),"AN","-")...lets call this TestA, which will either return AN or fail

     

    =IF(OR(C2="CEM",C2="NONCAT",C2="INFORMATION",C2="FINANCE",C2="CR",C2="PENSION",C2="UINV",C2="CTRLM",C2="JIPE",C2="CSARC"),"AN","-")....lets call this TestB whcih will again either return AN or fail...

     

    Finally you combine them:

    =IF(AND(TestA ="AN", TestB="AN"), "AN", "-")...meaning both separate test must return AN in order for formula to evaluate.

     

    Hopefully its a bit clearer...I got lost myself trying to dissect it and I wrote it like 2 hours ago 😄 

     

    Soon, we will have access to new function "LET" which will help with writing formulas like this.

     

    Best regards,

    • Nandhu19940's avatar
      Nandhu19940
      Copper Contributor
      Apr 12, 2020

      Branislav1984 Thanks lot for explaining me the concept and now i could do more better in IF conditions.

       

      Lets wait for LET function to come in future.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Apr 12, 2020

        Nandhu19940 Not that it matters much but just to demonstrate another, more dynamic, approach that does not require you to "hard-code" all the keywords into many nested IF statements. Create a list with keywords and give it a name. Then this will work as well:

        =IFERROR(IF(AND($A$2:$A$11=1,$B$2:$B$11=FALSE,$D$2:$D$11="--",MATCH(C2,keywords,0)),"Required Action"),"x")

        It's demonstrated in the attached workbook. Adopt the ranges to your real situation, give them names or use structured table references to make it really dynamic.

Resources