0

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

1
  • Welcome. Please share some test data. Even better, read how to and share a test sheet so as you can be easier helped. – marikamitsos Feb 22 at 19:00
0

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.

2
  • Works wonderfully ! – Fred Ronteix Feb 24 at 18:59
  • Great to hear it. Would you now take a moment to mark my post as "Best Answer"? It doesn't earn me anything of value. It's just a way to close out the issue and indicate that it has been fully resolved, as well as helping future site visitor more easily find the answer that best match their own questions. – Erik Tyler Feb 25 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.