I am currently using =regexmatch(A1,"(?i)N02|N22|J25") in column A to highlight the cells that contain these terms. But I don't want to hardcode it as the search terms keep changing. The search terms are in col. E or joint ones are in G1.

enter image description here

I've tried the following formulas but they don't work:




I found this formula in my search, and that person had the same issue as me. But I don't understand how I can get it to work for me.


Sample sheet https://docs.google.com/spreadsheets/d/1911rox5wAp1eyBULCSSOWrGwQ1wFWI3sjZJPsZqjOp8/edit#gid=69482816

2 Answers 2


Your conditional formatting custom formula rules are failing because they omit the required $ signs in the reference to the range E2:E6. To make it work, use a row absolute reference, like this:

=regexmatch(A2, "(?i)" & textjoin("|", true, E$2:E$6))

See Lance's explanation of how absolute and relative addresses work in a conditional formatting custom formula rule.


I found the answer to my question =REGEXMATCH(LOWER(A2),LOWER(TEXTJOIN("|",1,E$2:E$10)))

It is from this post in StackExchange. If cell y contains any word from range x:x, mark cell y green Thanks to user0 for the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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