Column if words as filter

Hi,

Try this array formula:

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

Regards

Does not work. It does appear to do the trick, but when I write "why is this happening" in A6 and write "why" in B2, then the sentence "why is this happening does not appear in C3.

I need the formula to allow the list in column A and B to be of such length that it is possible to add many more sentences and words, with the list of sentences in column C adapting accordingly.

If you try the google docs formula in a google docs spreadsheet, you will see that you will be able to write new sentences in A or new words in B, and the list in C will adapt accordingly, no matter how many new sentences or words you add.

Thank you for trying though, regards.

Any other suggestions?

So in essence, I need a formula that works in excel in the same manner as the google docs formula works in google docs

But this is nothing to do with the formula itself (did you extend the ranges to row 6 and re-calculate?) but only to do with the fact that the ranges have not been set dynamically.

I presumed that this was merely a secondary concern of yours (and something which you were capable of doing yourself), after obtaining the correct syntax for the formula in Excel. Apologies if this presumption was incorrect: there are several ways to make ranges dynamic, including:

Go to Name Manager and define two new ranges thus:

Name: Sentences
Refers to: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Name: Keywords
Refers to: =Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$B:$B))

(Obviously amend the column and starting references to match your actual set-up. I note that you also made no mention of the possibility of blanks anywhere between the first and last non-blank entries within either of these ranges, so this is assumed to be the case.)

Your (array) formula is then:

=IFERROR(INDEX(Sentences,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH(TRANSPOSE(IF(Keywords<>""," "&Keywords&" "))," "&Sentences&" ")),ROW(Sentences)),ROW(Sentences)),ROW(Sentences)),ROWS($1:1))),"")

and will respond dynamically to additions/deletions to either list.

Regards

You're welcome.