• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a if one word of a list exists in a cell [SOLVED]


New Member

Let's say in column A I have a list of cells with a lot of text in each cell.

In column B, I have a a bunch of key words that I would like to find in column A.

For each cell of column A, i would like to test if it contains at list one of the word of the list in the column B.

Can anyone help me ? Thanks in adavance,
countif and sumproduct my friend.. :)

in column C write, =SUMPRODUCT(COUNTIF(A1,$B$1:$B$10))

assumes B1:B10 has the keywords.

To find one word (or rather a string match), you can add use wildcards : =SUMPRODUCT(COUNTIF(A1,"*"&$B$1:$B$10&"*"))

Hope this helps.


Thanks for the formulas, it works great.

Do you think it is possible to find which word has been found (if a word of the list exists, it will be unique and appears only one time in a cell in column A)