Help!! Conditional formatting formula

[MichaelRSnow]

I'm not sure why this doesn't work but I'm trying to write 4 conditional formatting formulas but I'm getting multiple results

I want to A) look at cell for a specific value (looking to match 1 or none), could be multiple options in that one cell, if true or false then B) check another cell for one of two values. In my head there are 4 possible outcomes
A) Finds Value and B) Finds Value 1
A) Does not find any Values and B) Finds Value 1
A) Finds Value and B) Finds Value 2
A) Does not finds any Values and B) Finds Value 2

I was using IF(AND(OR with ISNUMBER to match or ISERROR to not match but something isn't right as I'm seeing duplicate results being matched

=IF(AND(OR(ISERROR(SEARCH("*One*",A1)),ISERROR(SEARCH("*Two*",A1)),ISERROR(SEARCH("*Three*",A1)),ISERROR(SEARCH("*Four*",A1))),C1="Yes"),1,0)
=IF(AND(OR(ISERROR(SEARCH("*One*",A1)),ISERROR(SEARCH("*Two*",A1)),ISERROR(SEARCH("*Three*",A1)),ISERROR(SEARCH("*Four*",A1))),C1="No"),1,0)
=IF(AND(OR(ISNUMBER(SEARCH("*One*",A1)),ISNUMBER(SEARCH("*Two*",A1)),ISNUMBER(SEARCH("*Three*",A1)),ISNUMBER(SEARCH("*Four*",A1))),C1="Yes"),1,0)
=IF(AND(OR(ISNUMBER(SEARCH("*One*",A1)),ISNUMBER(SEARCH("*Two*",A1)),ISNUMBER(SEARCH("*Three*",A1)),ISNUMBER(SEARCH("*Four*",A1))),C1="No"),1,0)

Example Results, what am I doing wrong?

One	Yes	1​	0​	1​	0​
Two	Yes	1​	0​	1​	0​
Three	Yes	1​	0​	1​	0​
One	No	0​	1​	0​	1​
Two	No	0​	1​	0​	1​
Three	No	0​	1​	0​	1​
Four	Yes	1​	0​	1​	0​
Four	No	0​	1​	0​	1​
Five	Yes	1​	0​	0​	0​
Five	No	0​	1​	0​	0​
	Yes	1​	0​	0​	0​
	No	0​	1​	0​	0​

 

[Eric W]

I see 2 issues with your formulas. First, you don't need the wildcard character with SEARCH. Second, I think the Boolean logic is not quite right. As you noted, there are 4 combinations, making this a binary problem. I'd suggest putting your valid values for the first column in a separate table, making it easier to read and change. This makes the formulas easier too:

Book3
	A	B	C	D	E	F	G	H	I	J
1	First		Second		Not found and No	Not found and yes	Found and No	Found and Yes		Value List
2	One		Yes		0	0	0	1		One
3	Two		Yes		0	0	0	1		Two
4	Three		Yes		0	0	0	1		Three
5	One		No		0	0	1	0		Four
6	Two		No		0	0	1	0
7	Three		No		0	0	1	0
8	Four		Yes		0	0	0	1
9	Four		No		0	0	1	0
10	Five		Yes		0	1	0	0
11	Five		No		1	0	0	0
Sheet7

Cell Formulas
Range	Formula
E2:E11	E2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=0,1,0)
F2:F11	F2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=1,1,0)
G2:G11	G2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=2,1,0)
H2:H11	H2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=3,1,0)

Hope this helps!

 

[MichaelRSnow]

I see 2 issues with your formulas. First, you don't need the wildcard character with SEARCH. Second, I think the Boolean logic is not quite right. As you noted, there are 4 combinations, making this a binary problem. I'd suggest putting your valid values for the first column in a separate table, making it easier to read and change. This makes the formulas easier too:

Book3
	A	B	C	D	E	F	G	H	I	J
1	First		Second		Not found and No	Not found and yes	Found and No	Found and Yes		Value List
2	One		Yes		0	0	0	1		One
3	Two		Yes		0	0	0	1		Two
4	Three		Yes		0	0	0	1		Three
5	One		No		0	0	1	0		Four
6	Two		No		0	0	1	0
7	Three		No		0	0	1	0
8	Four		Yes		0	0	0	1
9	Four		No		0	0	1	0
10	Five		Yes		0	1	0	0
11	Five		No		1	0	0	0
Sheet7

Cell Formulas
Range	Formula
E2:E11	E2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=0,1,0)
F2:F11	F2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=1,1,0)
G2:G11	G2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=2,1,0)
H2:H11	H2	=IF(ISNUMBER(MATCH($A2,$J$2:$J$5,0))*2+($C2="Yes")=3,1,0)

Hope this helps!

Sorry wasn't absolutely clear in my example, I need the wild cards as the text string in the cell is more than the value I'm looking for, i've updated my example below

Test One Test	Yes	1​	0​	1​	0​
Test Two Test	Yes	1​	0​	1​	0​
Test Three Test	Yes	1​	0​	1​	0​
Test One Test	No	0​	1​	0​	1​
Test Two Test	No	0​	1​	0​	1​
Test Three Test	No	0​	1​	0​	1​
Test Four Test	Yes	1​	0​	1​	0​
Test Four Test	No	0​	1​	0​	1​
Test Five Test	Yes	1​	0​	0​	0​
Test Five Test	No	0​	1​	0​	0​
	Yes	1​	0​	0​	0​
	No	0​	1​	0​	0​

 

[Eric W]

My apologies, I made a couple of errors. First, I stand by my assertion that you don't need the wildcards, but on the other hand, they don't hurt either. For example:

Book3
	A	B	C
15	Test One Test		6
16			1
Sheet7

Cell Formulas
Range	Formula
C15	C15	=SEARCH("One",A15)
C16	C16	=SEARCH("*One*",A15)

Both ways of writing the function return a positive value which would return TRUE from ISNUMBER.

Second, my formulas did not handle the fact that it's not a full match either. I still think it's easier to put your values in a list somewhere, so try this instead:

Book3
	A	B	C	D	E	F	G	H	I	J
1	First		Second		Not found and No	Not found and yes	Found and No	Found and Yes		Value List
2	One Test		Yes		0	0	0	1		One
3	Two Test		Yes		0	0	0	1		Two
4	Test Three		Yes		0	0	0	1		Three
5	Test One Test		No		0	0	1	0		Four
6	Test Two		No		0	0	1	0
7	Three Three		No		0	0	1	0
8	Four test		Yes		0	0	0	1
9	Fourteen		No		0	0	1	0
10	Five test		Yes		0	1	0	0
11	FFF Five		No		1	0	0	0
Sheet7

Cell Formulas
Range	Formula
E2:E11	E2	=IF(ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=0,1,0)
F2:F11	F2	=IF(ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=1,1,0)
G2:G11	G2	=IF(ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=2,1,0)
H2:H11	H2	=IF(ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=3,1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:H11	Expression	=ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=3	text	NO
G2:G11	Expression	=ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=2	text	NO
F2:F11	Expression	=ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=1	text	NO
E2:E11	Expression	=ISNUMBER(AGGREGATE(14,6,SEARCH($J$2:$J$5,A2),1))*2+($C2="Yes")=0	text	NO

Also, please update your profile to show what version of Excel you're using. The best answer often depends on what functions you have available to use.