Nested CountIF

[Koda7]

I have a tab full of data and want to create a formula that will search 2 cells within each row to search for specific words.

I was using a formula (when I only had one word I needed to search for):

=IF(COUNTIF(G2:H2,"*cloud*"),"cloud","")

However, now I need to create a new column which will look in the same 2 columns but for other words. I tried to use a nested IF(CountIF formula, but no luck.

Oranges
Apples
Pears
Cranberries
Pumpkins

Ultimately, I want to look in G2&H2 and if any of those words appear, I'd like the formula to return the word "Fruit", otherwise, leave the cell blank. Thanks very much for your help.

[tigeravatar]

Koda7,

Give this a try:

=IF(SUMPRODUCT(COUNTIF(G2:H2,"*"&{"oranges","apples","pears","cranberries","pumpkins"}&"*")),"Fruit","")

[Koda7]

thanks so much! Worked like a charm!