How to search a range for a matching word within a string?

Question

What I'm trying to do is this:

Cell A1 contains a sentence. The dog had black spots all over

Column B contains a range of keywords. Let's say B7 contains dog.

What would be the formula to get C1 to return dog if the string to search was A1 and the range containing any matching words was in column B?

So

1. look at A1 for the string
2. search column B for any matching words
3. output matching word/words to C1

Answer 1

Judging by the description, your data is presented in this way:

	A	B
1	The dog had black spots all over	Keywords
2		well
3		over
4		steps
5		him
6		didn't
7		dog
8		that
9		all
10		behind
11		quiet
12		who
13		any
14		could

First of all, we divide the phrase from A1 into separate words:

=SPLIT(A1," .,-")

In the second parameter, list all punctuation marks that may occur in the analyzed phrase, add ! ? : ; - this will allow you to clear individual terms as much as possible.

As a result of executing the SPLIT() function, you will get the following array:

Now let's find each word from the Keywords column using the MATCH() function. This function will return the relative position of the word in the array, or an error if there is no such word. We don't need a position, the fact itself is important to us - a word is found or not found. Therefore, we will wrap this function in ISNUMBER()

=ArrayFormula(ISNUMBER(MATCH(B1:B,SPLIT(A1," .,-"),0)))

For our example data, this would return an array like this:

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE...}

We use this array in the FILTER() function as condition1. And in case there are duplicates in the Keywords column, we wrap our entire formula in the UNIQUE() function

=UNIQUE(FILTER(B1:B,ISNUMBER(MATCH(B1:B,SPLIT(A1," .,-"),0)) ))