Column if words as filter

Hi

I have a column of sentences in column A and a column of single words in column B. I want those sentences in column A that contain one or more of the words in column B to appear in a new column; column C.

I also want the formula to work in such a manner that if a new word is written in the column B or a word is deleted, or if a sentence in column A is replaced or removed or a new is inserted, the list in column C will update automatically according to the changes in column A and B.

The length of the column of sentences and words in A and B must hence be able to change, and hence so must the length of the list of sentences in column C.


This works in google.docs with the following formula:

=FILTER('Sheet1'!A1:A;MMULT(SEARCH(TRANSPOSE(" "&B1:B&" ");" "&'Sheet1'!A1:A&" ");SIGN(ROW('Sheet1'!A1:A))))


After several rounds of feedback from forum users, I have tried entering these formulas in excel (as arrays in the correct manner), but none of them can do the task:

=IF(SUMPRODUCT((ROWS($B$1:$B$4)*(COUNTIF(A1,"*"&$B$2:$B$4&"*"))))>0,A2,"")

=IF(OR(ISNUMBER(SEARCH($B$1:$B$1,A1))),A1,"")

=IF(COUNT(INDEX(SEARCH($B$1:$B$4,A1),0)),A2,"")

=IFERROR(INDEX(A:A,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH(TRANSPOSE($B$1:$B$4),$A$1:$A$5)),ROW($A$1:$A$5)),ROW($A$1:$A$5)),ROW($A$1:$A$5)),ROWS($2:2))),"")


Some of these produce a list of sentences in C, but not in the manner described that works with the google docs formula.

I can hardly believe that this is possible to do in google docs but not in excel. I want to use excel because the processing capacity in google docs is rather limited when applied to large amounts of data, compared to excel.


If anybody is able to help it is greatly appreciated.


Regards, Njaal, Norway.