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

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`
• Welcome! Do you mean something like as `=UNIQUE(FILTER(B1:B,ISNUMBER(MATCH(B1:B,SPLIT(A1," .,-"),0))))`? Aug 31, 2022 at 6:38
• @JohnSUN Please turn your comment to an answer so it can be accepted and help others as well. Aug 31, 2022 at 15:33
• @Kyle Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Aug 31, 2022 at 17:38

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)) ))`