Google Sheets – Check if Cell Contains String from Range of Cells

google sheets

I have a spreadsheet with a list of job titles in column A and a list of specific words in column C (C2:C13) ('researcher' ,'scientist' for example). All I want is to fill column B with TRUE if the cell in column A contains one or more of the strings in the range C2:C13.

I have seen many posts about this already, but unfortunately none of them have work for me:
=VLOOKUP(A2,INDIRECT("'jobTitles'!C2:C13"),1,FALSE)=A2 (I don't know why, even with the named range jobTitles created)

=IF(ISERROR(MATCH(A2;C2:C13;0));"Not found";"Found")

=SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 (same as above…)

Best Answer

Keep in mind that we can't see what you see unless you share a link (or at least an image that clearly represents all facets of the issue). However, if I'm correct in understanding the post, you can try this in B2:

=ArrayFormula(IF(A2:A="",,NOT(ISERROR(REGEXEXTRACT(LOWER(A2:A),JOIN("|",FILTER(LOWER(C2:C),C2:C<>"")))))))

The JOIN clause concatenates all non-blank entries in C2:C with a pipe symbol which, in REGEX functions, is the "or" marker.

If the REGEXEXTRACT is able to find any of those concatenated "ors" in each cell of A2:A, then normally, it would return the first match; and if no match were found, you'd get an error.

So NOT(ISERROR(...)) works with that information to give you the Boolean TRUE or FALSE.

LOWER( ) just makes sure that variations in capitalization won't affect matches, since everything is reduced to lowercase in the virtual space this way.

Related Topic