Search multiple cells for keywords, and display a result based on which word is found

[Captain Hindsight]

I have found the formula below which half applies to my problem.

This displays the correct word, depending on whether cell A1 contains that exact text only (cat, dog or mouse).

=VLOOKUP(A1, {"Cat","Purrs";"Dog","Barks";"Mouse","Squeaks"},2,0)

However I want it to display purrs, barks or squeaks if the cells contain the word cat, dog or mouse in them anywhere e.g. if cell A1 said "xx cat ht".

How can I do this please?

 

[XOR LX]

Hi,

=LOOKUP(2^15,SEARCH({"Cat","Dog","Mouse"},A1),{"Purrs","Barks","Squeaks"})

Regards

 

[Captain Hindsight]

Hi,

=LOOKUP(2^15,SEARCH({"Cat","Dog","Mouse"},A1),{"Purrs","Barks","Squeaks"})

Regards

Fantastic, thank you.

 

[XOR LX]

You're welcome!

 

[Captain Hindsight]

My formula looks like this:

=LOOKUP(2^15,SEARCH({"mechanical"},'All Responses'!$F2),{1})

However I want it to look at 'All Responses'!$F2:$G2 and see if either of those cells contain the word "mechanical".

This seems to prevent the formula from working correctly as some cells which worked when I just had F2, now do not return the 1 when I look at both F2 and G2.

Any suggestions?

 

[XOR LX]

My formula looks like this:

=LOOKUP(2^15,SEARCH({"mechanical"},'All Responses'!$F2),{1})

However I want it to look at 'All Responses'!$F2:$G2 and see if either of those cells contain the word "mechanical".

This seems to prevent the formula from working correctly as some cells which worked when I just had F2, now do not return the 1 when I look at both F2 and G2.

Any suggestions?

What have you done to that formula?!

So you're only searching for one word now? And what do you want the formula to return if either of those two cells contains that word? And if neither does?

This is in effect the reverse of what you originally asked for: there you were searching for mutiple strings within a single cell; now you're searching for a single string in multiple cells! Make your mind up!

Regards

 

[Captain Hindsight]

I realised I needed to split those multiple words into having one column each in this tab I'm working in to achieve what I want, so yeah in effect have now reversed my requirement.

I want it to return a 1 if either of the cells contains that word. If neither does I would like it to be blank, so "".

Thanks

 

[XOR LX]

=IF(COUNTIF('All Responses'!F2:G2,"*mechanical*"),1,"")

Regards

 

[Captain Hindsight]

=IF(COUNTIF('All Responses'!F2:G2,"*mechanical*"),1,"")

Regards

Perfect

 

[XOR LX]

You're welcome again!

Cheers