1

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
3
  • Welcome! Do you mean something like as =UNIQUE(FILTER(B1:B,ISNUMBER(MATCH(B1:B,SPLIT(A1," .,-"),0))))?
    – JohnSUN
    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

1 Answer 1

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:

First result

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

Result

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.