IF OR and AND formular, help!.... I want to use so much criteria, Im toally stumped..

[novabond]

hello.

Im really stuck, I hope someone can help.

The formular I am looking for has this criteria:

• formular in cell S28 (i will refer to this cell as Output cell)
  • If there is a "P" in cell $BA28 (I will refer to this cell as Include cell)
    • value if true :
      • If there is text in either S$11 OR S$15 (I will refer to this range as set A) and it matches part of text in $BN28 (i will refer to this cell as Lookup cell)
      • AND
      • If there is text in either S$19 OR S$23 (i will refer to this range as set B) and it matches part of text in $BN28 (as above)
        • = "P"
    • value if false :
      • • = "" (blank)

note, (as i want to try, and best explain the criteria still)
so it will also be that,

If there is a "P" in Include cell
and
If there is text in one or both cells is set A, and both or one of these text doesnt match part text in Lookup cell
AND
If there is text in one or both cells is set B, and both or one of these text does match part text in Lookup cell
or visa versa
I need this to return "" (blank) in the Output cell

and

I was also struggling with a simple way to get the formular, not to recognise an empty cell in sets A or B, as being matched as TRUE, with part text in the Lookup cell

btw, I use "P" in cells, whereby I was to display a tick, by also having font Wingdings 2......

and

as per the illustration image below, I will be copying this formular to other cells.

but, I can eaisly rename the cells in the formular myself, if you want to write this formular uisng the first cells in a worksheet say:
(NB: I wanted to include my actual cell references earlier, incase it becuase relevent in the overall solution, I also cant get XL2BB to work for me at the moment, so cant drop in minisheets to help)
A$1:A$2, set A
A$3:A$4, set B
A5, Output cell
$J5, Include call
$K5, Lookup cell

thanks so much for reading this!... fingeres crossed someone wants to take it on... tnanks again !

 

[novabond]

Check below -

As my suspicion was, you had put wrong formula

Hi, sorry my internet is playing up and has been confusing what i have sent..

Ive been trying to reply ´´wowzers, your formular is great, it seems i managed to past it twice in a cell, apoligies i didnt properly check that in the fist place!... and you may have noticed that i just sent a snip of the only issues that remains, which is just when both cells in one of the sets are blank but, the result should be "P"... see what you think... tnanks again!

 

[SanjayGMusafir]

Sorry for some delay... I have rethink ALL the conditions you wanted and reformulate accordingly...

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2																			Include	Lookup
3		Result of Formular should be			P	P	P	P				P	P	P		P		P
4		set A	yep	out	yep		yep		out		out	out		yep	yep	yep	out
5			out	out	out	in					out	yep		out	in	in	out
6		set B	out	yep	in	yep		yep	out		yep		out	in	out		yep	yep
7			out	out		out							yep	out	out		in	in
8		Formular			P	P	P	P				P	P	P		P		P	P	yep in
Sheet3

Cell Formulas
Range	Formula
C8:I8,K8:R8	C8	=IF(AND($S8="P",AND(OR(AND(C$4="",C$5=""),OR(AND(C$4<>"",IFERROR(FIND(C$4,$T8),0)<>0),AND(C$5<>"",IFERROR(FIND(C$5,$T8),0)<>0))),OR(AND(C$6="",C$7=""),AND(C$6<>"",IFERROR(FIND(C$6,$T8),0)<>0),AND(C$7<>"",IFERROR(FIND(C$7,$T8),0)<>0)))),"P","")

 

[novabond]

Sorry for some delay... I have rethink ALL the conditions you wanted and reformulate accordingly...

hey no problem, that is fantastic!... the only difference with how it is working for me is, if both cells in both sets are blank, it results in "P" rather than blank... im sure i could take it from here and add a coutif or somthing but, it would be wonderful if you had a better idea or why it might not be working for me?... 5 days i have been working on this, so thank you so much, its such a hugh help...

 

[SanjayGMusafir]

if both cells in both sets are blank, it results in "P" rather than blank...

I initially used that as condition then thought probably you won't need that and removed it.

Also note, now I have used IFS function to eliminate hazard of closing extra brackets.

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2																			Include	Lookup
3		Result of Formular should be			P	P	P	P				P	P	P		P		P
4		set A	yep	out	yep		yep		out		out	out		yep	yep	yep	out
5			out	out	out	in					out	yep		out	in	in	out
6		set B	out	yep	in	yep		yep	out		yep		out	in	out		yep	yep
7			out	out		out							yep	out	out		in	in
8		Formular			P	P	P	P				P	P	P		P		P	P	yep in
Sheet3

Cell Formulas
Range	Formula
C8:R8	C8	=IFS(COUNTBLANK(C$4:C$7)=4,"",AND($S8="P",AND(OR(AND(C$4="",C$5=""),OR(AND(C$4<>"",IFERROR(FIND(C$4,$T8),0)<>0),AND(C$5<>"",IFERROR(FIND(C$5,$T8),0)<>0))),OR(AND(C$6="",C$7=""),AND(C$6<>"",IFERROR(FIND(C$6,$T8),0)<>0),AND(C$7<>"",IFERROR(FIND(C$7,$T8),0)<>0)))),"P",TRUE,"")

 

[novabond]

I initially used that as condition then thought probably you won't need that and removed it.

Also note, now I have used IFS function to eliminate hazard of closing extra brackets.

that is it! worked for me... brilliant... thank you so much Sanjay... I can now move on to the next whatever may crop up... .thanks again and happy friday, i hope you have a super weekend.... lovely

 

[SanjayGMusafir]

that is it! worked for me... brilliant... thank you so much Sanjay..

Glad to help you

 

[novabond]

Glad to help you

although..... i have just noticed that the formular is case sensertive... i dont supose it is simple to make it not be? is it a little tweek do you think?

 

[novabond]

although..... i have just noticed that the formular is case sensertive... i dont supose it is simple to make it not be? is it a little tweek do you think?

Glad to help you

ah! i changed FIND to SEARCH which i think is what you may have recomended!?... works great now, and good to have the choice!...