Simple If then else for getting output

[Dinesh_Excel]

Hello Friends,

Have three columns along with a "Output Colum" with some targeted Text as "SCOPE"

1. Column 1 have workgroups from 1 to 7 along with some other texts
2. Column 2 has txt like "BDF " Seal"MDM" VDM"along with some other texts
3. Column "Authorization" along with some other texts

Criteria to print output as "Scope"




Output is = Should print "Scope" in Column output if Column 1 has WG1…WG7 and should not consider text like "BDF " Seal"MDM" VDM" from column 2 and Column 3 should not consider text" Authorization"


Appreciate your help on the same.

Thank you

 

[SirJB7]

Hi, Dinesh_Excel!
Would you please update the uploaded file with more example lines that gives an output result different from "Scope"?
Explain also if the "WG1...WG7" should be all present in the source and in what order, if ascending, if all together...
About the source, is column A or another cell?
Please elaborate.
Regards!

 

[Dinesh_Excel]

Hello Sirjb7,

Thank you for your response.

let me explain you the criteria:
Please refer the new attachment.

1.Find texts which Includes these work groups having texts as WG1,WG2, wg3 ,wg4,wg5,wg6 from Column 1.

2.Similarly you will find combination of
BDF,seal,mdm,vdm in same column 1 along with WG1 to WG6

3.In column 2 you will find text "Authorization"

output = if column 1 has wg1 to wg7 which should exclude words like BDF,seal,mdm,vdm and in column 2 it should exclude text"Authorization"


Then in out put column it should print "Scope" else "Not in scope"

Please refer the attachment it is self explanatory.

thank you again

 

[bosco_yip]

1] In H3, copy down :

'=IF(AND(SUMPRODUCT(0+ISNUMBER(FIND($E$3:$E$8,B3))),1-SUMPRODUCT(0+ISNUMBER(FIND($F$3:$F$6,B3))),1-ISNUMBER(FIND($G$3,C3))),"Scope","Not in Scope")


2] see attached file

Regards
Bosco

 

[Dinesh_Excel]

Thanks Bosco it was very helpful.
I was trying to understand this formula while I added one more column called"Column Auth" with excluded list from column h.

Similar to below but its throwing some error"

Please help. Attached file for your reference.

=IF(AND(SUMPRODUCT(0+ISNUMBER(FIND($E$3:$E$8,C3))),1-SUMPRODUCT(0+ISNUMBER(FIND($F$3:$F$6,C3))),1-ISNUMBER(FIND($G$3:$G$6,D3))),1-ISNUMBER(FIND($H$3:$H6,B3)))"Scope","Not in Scope")