Search one cell for specified words

[Shoey68]

Hi there,
I'm trying to count if 1, 2 or all 3 of a selection of words appear in certain cells.
For instance. Three cells might contain:
A1 "The cat sat on the mat"
A2 "The dog chased the ball"
A3 "Then the dog sat on the mat"

I need a formula to identify whether or not each cell contains the words "cat", "sat" or "mat", but not count the occurences.
The result should be like:
B1 "1" (all three words appear, but I need Excel to count only 1 occurence)
B2 "0" (none of the words appear)
B3 "1" (2 words appear, but agian I need Excel to count only once"

Apologies if I haven't explained this very well and thanks in advance for your help!!

 

[Ron Coderre]

Here you go...I didn't realize the formula I posted would be an array-formula.
This one is a regular formula:

B1: =--OR(INDEX(ISNUMBER(SEARCH(" "&D$1:D$3&" "," "&A1&" ")),0))

That should work....Does it?

 

[Ron Coderre]

That's superb!!

Worked a treat

Thanks for all your help!!!

That's a nice, concise formula (per usual from Mr. Valko).

This may not be an issue, but...Just be aware that it doesn't differentiate between standalone words and embedded words.
In this example, it would think that "cat" is a match:
"The catcher in the rye"