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

  • Welcome to Web Applications. This question is not a good fit for this site. Did you know google-sheets is this site's most popular and active tag? Because of this, at least we, myself and my alter ego, are super strict, downvoting and closing early and often posts that don't help attract Google Sheets experts. You might help this site achieve the dream of being a library of high-quality questions and answers by describing the relevant parts of your spreadsheet, showing what you have tried, and showing the most relevant posts and why they didn't meet your needs.
    – Rubén
    Jul 22 at 14:28

1 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().

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.