Find a phrase

Hi I've attached an example document to make this easier.

In sheet one column B I have a rows of text. In sheet 2 I have two tables with different sets of phrases.

What I would like to do is have row C on sheet 1 to look and see if the text in column B matches either of the tables on sheet 2 and output either 1 or 2 to represent which table it was found in. If the text is found in both tables then the output should be 2. If only part of the phrase in col b in found in either table then output should be 0.

I've put some example in the attached doc.

Cheers

Dave

Originally Posted by canapone

Hi,

a first approach: the two outcome table ranges cannot house blank cells. Otherwise you have to reduce ranges read by SEARCH (the red numbers).

=MAX(ISNUMBER(LOOKUP(-1,-SEARCH(TRIM(Sheet2!E$7:E$31),B9)))*2,ISNUMBER(LOOKUP(-1,-SEARCH(TRIM(Sheet2!B$7:B$31),B9)))))

Regards

Edit: sorry Special-K did not mean to overlap. I'm a very slow writer.

Hi that's perfect, I won't be adding a lot of phrases so I will just increase the ranges to the amount I need.

Many thanks

Thank you as well Special K.