Google sheet: find a Word from a range, in a string and return corresponding Word from the range

Question

Column A contains text strings Range aa:bb (in another sheet): aa contains keywords bb contains result-Words

I would like to put a function in for example cell B2 which: Checks If any of the Words in column aa is in cell A2, and gives the corresponding Word from column bb as a result in cell B2

Cell A2: «my New car»

Cell B2: Red

Col aa: Car, Boat …

Col bb: Red, Green …

Rather not a matrix formula and not a crazy long If function. 😀 It should not be case sensitive

I think the problem is simple, but difficult to solve with standard Google sheet functions. Thanks a lot Leif

Answer 1

Use filter() and regexmatch() or search(), like this:

=filter(Sheet2!BB2:BB, regexmatch(Sheet2!AA2:AA, "(?i)" & A2))

See filter(), regexmatch() and search().