How to check for multiple keywords on a text and return back one or more creteria

[irman]

Hi Guys

I am looking for a formula to help me like the below example

[TABLE="width: 500"]
<tbody>[TR]
[TD]brown, blue, green, black [/TD]
[TD] if Cell A1 contain Blue and black and green return X1 in Cell B1 [/TD]
[/TR]
[TR]
[TD]Car, Black, Battery, Monitor [/TD]
[TD] if Cell A2 contain Black and battery and car return X2 in Cell B2[/TD]
[/TR]
[TR]
[TD] Apple, Carnot, blue, brown[/TD]
[TD]if Cell A3 contain Apple and carrot and car return X3 in Cell B3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Domenic]

Try...

=AND(ISNUMBER(SEARCH(","&{"blue","black","green"}&",",","&SUBSTITUTE(SUBSTITUTE(A1,", ",",")," ,",",")&",")))

or

=AND(ISNUMBER(SEARCH(","&G1:I1&",",","&SUBSTITUTE(SUBSTITUTE(A1,", ",",")," ,",",")&",")))

...where G1:I1 contains the colors of interest, such as blue, black and green. Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER. Also, note that both formulas deal with situations where the data contains extra or no spaces, such as the following...

brown,blue,green,black
brown ,blue , green, black
etc...

Hope this helps!