Searching whether a cell contains one or more keywords from a dynamic list

Hello
I'm new to this forum.
I've used Excel only for vanilla projects and I'm trying to do something more complicated.
I'd like to create a dynamic list of keywords - something I can change on occasion, and apply this list to column with text entries.
If any keyword from the list is contained within a cell's text, I'd like to copy another cell to the next column (in order to sum the new column).
I'm attaching a mock-up spreadsheet for illustration purposes.
Any ideas?
Thanks for your help.
Georgi

Put your keywords in individual cells, for example D2, E2 and F2 might contain keywords, keyword1 and keyword2 respectively, then you can use this formula in D4:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(D$2:F$2,B4))))>0,C4,"")

and copy down as far as you need to.

Hope this helps.

Pete

Originally Posted by Pete_UK

Put your keywords in individual cells, for example D2, E2 and F2 might contain keywords, keyword1 and keyword2 respectively, then you can use this formula in D4:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(D$2:F$2,B4))))>0,C4,"")

and copy down as far as you need to.

Hope this helps.

Pete

Thanks Pete. This is very helpful.
I was looking to have a dynamic list of keywords, but I can replace that with a big-size fixed-length array of keywords and stuff the array with "dummies", leaving space to add more keywords as needed.
Thanks again!
Georgi

Well you could have a dynamic named range (eg called "keywords") and then the formula would be:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(keywords,B4))))>0,C4,"")

Then you could put your keywords anywhere (eg in column K, starting with K1) and then define the named range with this in the refers to box:

=INDIRECT("Sheet1!K1:K"&COUNTA(Sheet1!$K:$K))

and this will automatically adjust to the number of entries you have in column K.

Hope this helps.

Pete