Checking for partial matches in Google Sheets

Question

I am trying to come up with a solution to check if the values in column E are partially contained in column B. However, as can be seen in the picture below, the code I have written yields incorrect results sometimes (the first one is a false negative - it is indeed correct).

The code I have used is:

=IF(ISNUMBER(SEARCH(LOWER(E2), LOWER(B2))), "ok", "check")

Are there any improvements I can make to further refine the code?

Here's an extract of the Google Sheets document I am using:

https://docs.google.com/spreadsheets/d/1WYSvUYeSn9maqU8W-RpvSolSl6vFrt8t6wX7QNPbP10/edit?usp=sharing

Answer 1

Try:

=ARRAYFORMULA(IF(REGEXMATCH(lower(B3:B),lower(SUBSTITUTE(C3:C,",",".+"))),"ok","check"))

Result:

Explanation:

Using REGEXMATCH() this is the same as the SEARCH() function however it is case sensitive so we have to use LOWER(). And based on your condition it has to match all words if there are multiple words which are separated by comma. For that we use the SUBSTITUTE() to replace the comma with ".+" so it has to match all the words.

References:

• REGEXMATCH()
• SUBSTITUTE()
• Regex match two words in a string