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.
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.
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.
=SPLIT(TEXTJOIN(";",1,UNIQUE(FILTER($B$2:$B1000,REGEXMATCH(A2,$B$2:$B1000)))),";")