How can I return more than one match result from the same search key? [closed]

Question

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.

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.

Answer 1

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.