string word search and match list of words in column

[HRPRGMNGT]

Hi

I have 3 columns, Column A has comments and column B and Column C have a list of words that we want our string to compare to. For example:

Column A Column B Column C
I think that service is great Great Bad
Products are good service is bad Good Horrendous

what I need is to see if any words in column B match my string in Column A and the same with Column C do the words in this column C match any word in my string in Column A.

For my first comment I want a 1 for column B because it matches on 'great' and a 0 for column C because it has no matches. For my second comment I want a 1 for column B because it matches on 'good' and a 1 on column C because it matches on 'bad'.

Can someone help me with a formula that does this?

 

[T. Valko]

Hi

I have 3 columns, Column A has comments and column B and Column C have a list of words that we want our string to compare to. For example:

Column A Column B Column C
I think that service is great Great Bad
Products are good service is bad Good Horrendous

what I need is to see if any words in column B match my string in Column A and the same with Column C do the words in this column C match any word in my string in Column A.

For my first comment I want a 1 for column B because it matches on 'great' and a 0 for column C because it has no matches. For my second comment I want a 1 for column B because it matches on 'good' and a 1 on column C because it matches on 'bad'.

Can someone help me with a formula that does this?

Try these...

For matches on column B:

=COUNT(LOOKUP(1E100,SEARCH(B$2:B$3,A2)))

For matches on column C:

=COUNT(LOOKUP(1E100,SEARCH(C$2:C$3,A2)))

 

[HRPRGMNGT]

Thanks, you are absolutely amazing

 

[T. Valko]

Thanks, you are absolutely amazing

You're welcome. Thanks for the feedback!