Forum Discussion
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 | -- |
| 1 | FALSE | FINANCE | -- |
| 1 | FALSE | CR | -- |
| 1 | FALSE | PENSION | -- |
| 1 | FALSE | UINV | -- |
| 1 | FALSE | CTRLM | -- |
| 1 | FALSE | JIPE | -- |
| 1 | FALSE | CSARC | -- |
My condition:
If COUNT=1 and COM-RESULT =FALSE and LKVALUE-2 = -- and LOOK-VAL1 contains "CEM" or "NONCAT" or "INFORMATION" or "FINANCE" or "CR" or "PENSION" or "UINV" or "CTRLM" or "JIPE"or "CSARC" then my output is "Required action".
Formula so far tried:
1) =IF(OR(AND(C2="CEM", A2=1, B2="FALSE", D2="--"), AND(C2="NONCAT", A2=1, B2="FALSE", D2="--")), "Required action", "NA")--> Here implemented for first two values for LOOK-VAL1 column (CEM & NONCAT)
Result of above formula working correctly for first row value (i.e-if C2= "CEM") but not for second row value(NONCAT)
2)=IF(OR(C2="CEM", C2="NONCAT", C2="INFORMATION" , C2="FINANCE" , C2="CR" , C2="PENSION" , C2= "UINV" , C2="CTRLM" , C2="JIPE" , C2="CSARC"), "Required action", "NA")
Result of above formula is only comparing the C column values but i couldn't able to add conditions related to column A , B and D.
Kindly let me know any possible solution for the above problem statement.
- 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,
8 Replies
- 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,Dear Bran,
Your formula works perfectly and kindly explain me the concept behind that one.
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,
