Find keywords within statement.

In previous blog, we found number of words from keyword list in a statement. Now, we are interested to know specifically which words are actually found from keywords list . For Column B formula refer previous blog.

{=IF(COLUMNS($C2:C2)<=$B2,INDEX(keyword,SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))),"")}

It's an array formula, Enter this formula in C2 cell and press ctrl+shift+enter instead just enter, then drag down and  right to other cells.

Note:

text :  =SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,".",""),",","")     'Defined name to this formula as "text" in our example

keywords : =Sheet2!$A$1:$A$12  

morning

people

fabric

nobody

cabinet

sauce

cynical

time

temper

curious

difficult

beneath

'Below keywords are in Sheet2 , defined name to this formula as "keyword" in our example 

Briefly understanding formula :
i.e {=IF(COLUMNS($C2:C2)<=$B2,INDEX(keyword,SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))),"")}

In our example, our maximum word search from a keyword list is 3, so the column should be 3. Column for word1, word2 and word3.

First we need to know , If the columns is less and equal to the number of keywords found, then we will continue to fill the each column cell with a word that are found in a statement from a keyword list, else column will be blank. So we started formula from " {IF(COLUMNS($C2:C2)<=$B2,[value_if_true], "")}" to look if the number of columns is less and equal to the number of keywords in a statement.

Then, if we found it TRUE, the we will look for the words that are found . For that use INDEX function.

Syntax : INDEX(array, row_num , [column_num])

Here, array is "keyword" as we have to look a word from the keyword list . Further we need to find row_num.

For the row_num : We need to find the row_num of the found words in a keyword list.

We use SEARCH function to find the position of keyword in a statement . SEARCH function formula , detail explanation is on previous blog. Since, SEARCH is useful to find the position , so we used ISNUMBER function to know which words position are found and ISNUMBER brings it as TRUE if it found the position and FALSE if not. Now, we are interested to know the ROW number for TRUE values of ISNUMBER function. Use IF function and ROW function ([value_if_true]).

{................. IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)...........}

Now, As we said before number of keywords found = total number of columns.

Use SMALL function with COLUMN function, as lets say if column function result is 1, then small function will look the smallest row number of the found words from keyword list. If column function result is 2, then small function will look the second small row number of the found words from keyword list and so on.

 {.....SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))...}

So here , our formula completes :

{=IF(COLUMNS($C2:C2)<=$B2,INDEX(keyword,SMALL(IF(ISNUMBER(SEARCH(" "&TRIM(keyword)&" "," "&text&" ")),ROW(keyword)),COLUMNS($C2:C2))),"")}

It's an array formula, Enter this formula in C2 cell and press ctrl+shift+enter instead just enter, then drag down and  right to other cells.

Thank you !