1

Link to sheet used: Testing Sheet

Thanks to another answer, I was able to match and return a single word from a search_key being compared to a specific range using the following formula:

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

The spreadsheet shown is fictitious but represents what I'm attempting to do. I am trying to take the formula a step further now, where my search key contains 2 words that I want to be returned in the results.

Match two words from a single search key

Here is the issue that I'm running into with the original formula.

  1. In C4, I get N/A because B2 is two words, and I believe it only searches for 1 word due to column a string getting split into individual words. D4 is the result I am trying to achieve.

  2. C14 and C15 are the results I want; however, they are returned vertically in separate cells instead of one sell OR horizontally. I am not sure how to Filter to return horizontally.

I have played around with various functions being added to the formula, but none of them achieve the results that I'm looking for in columns D-F. Concatenate will join the results, but they are returned out of their original order, as shown in column A. What I am working on requires them to be returned in order, either in the same cell or horizontally.

Improve this question
4
  • 1
    Share a link to an actual spreadsheet with sample data and expected result already added, so that the volunteer contributors can build and test solutions if they feel inclined. Expecting the volunteers to set up their own spreadsheet and hand enter your data before they could even start to think about the problem will be a deterrent to many. Help us to help you. Share a link.
    – Erik Tyler
    Sep 2, 2022 at 3:41
  • Nice to see you again. Now, when it comes to cars, not dogs, the task turns the other way. You will not be able to get the desired "Kraton 4S", but only "Kraton", because that is how it is indicated in column B. And the problem will be solved by the not very simple formula =SPLIT(TEXTJOIN(";",1,UNIQUE(FILTER($B$2:$B1000,REGEXMATCH(A2,$B$2:$B1000)))),";")
    – JohnSUN
    Sep 2, 2022 at 7:24
  • Thank you again @JohnSUN. Kraton 4S was a mistake, as I quickly just typed this up for an example. Is there any way to return the results in the same order that they occur in Column A? For instance, if B2 was "6S" and B8 was "Mojave", this would return "6S Mojave". How do I get it to return "Mojave 6S"?
    – Kyle
    Sep 2, 2022 at 19:14
  • While a linked spreadsheet might be helpful, posts on this site should be self-contained. This means the question should include all the required details directly in the post body, including sample data and the expected calculation results.
    – Rubén ♦
    May 31 at 18:08

1 Answer 1

Reset to default
0

Assuming that all phrases in column A2:A follow the same pattern, you can get a list of vehicles mentioned there with a regular expression, like this:

=arrayformula( 
  iferror( 
    split( 
      regexextract(A2:A, "(?:included \w+ )(.+?)(?: as well as|$)"), 
      "|", false, true 
    ) 
  ) 
)

The formula does not pay attention to the vehicles listed in column B2:B.

To learn the exact regular expression syntax used by Google Sheets, see RE2.

Improve this answer

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