IF formula implementation with multiple logic

[Nandhu19940]

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.

[#2 · Accepted solution]

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,

[#3]

@Branislav1984 

Dear Bran,

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

[#4]

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 :D 

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

Best regards,

[#5]

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

[#6]

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

[#7]

@Riny_van_Eekelen Such a wonderful way of minimizing the lengthy formula thanks lot Riny.

:)

Queries:

1) Here in my count, com-result column values will differ and moreover rows can't be fixed in this section.

2) Dynamic rows will be there and need to implement formula accordingly so i tried below thing.

Formula:

=IFERROR(IF(AND(A2=$H$2,B2=$H$3,D2=$H$4, MATCH(C2,keywords,0)),"Required Action"),"x")

Because in your formula design i could see three column rows range is fixed but that won't workout for me and moreover my result for above formula is quite different because of using IFERROR it throws me FALSE when we not get match for A2, B2 & D2 column values with fixed one.

Performance issue: 

Another on thing here is my workbook has many rows (100000 + rows) data, so when i'm implementing the formula it's very slow and taking more time to implement for all rows.

So need to give work around on performance as well and let me know any tips you can avail for performance improvement in macro running.

Issue:

Calculating 2 processors (2%), i'm getting this one while running  my macro and taking too long time to finish it.

[#8]

@Nandhu19940 Sorry, but I don't know enough about the technology "behind" all of it to understand what to do or not to do to boost performance.

[#9]

@Nandhu19940 Based on your altered question, I have inserted your formula in my original sheet (attached) and it seems to work as intended. IF all four conditions are met (1, FALSE, -- and found in the list of keywords), THEN the required action will take place ELSE something else. That's exactly the point of the IFERROR statement. You still need to specify what needs to be done when the outcome of these four tests is FALSE. But perhaps I don't really understand the issue.