In Google Sheets, I have three columns (with different lengths!)

Column1 contains firm names (50)
Column2 containts firm names (100)

I would like to mark a cell in Column1 green (or with a number in the cell next to it) if the cell contains any of the names that appear in column2.

For example:

enter image description here

I tried it several times but I'm still having trouble. Here is a better example of my columns This is how the code currently works

for column a: =REGEXMATCH(LOWER(A2),LOWER(TEXTJOIN("|",1,B$2:B))) for column b: =REGEXMATCH(LOWER(B2),LOWER(TEXTJOIN("|",1,A$2:A)))

I would like to mark/match EXAMPLE 1 with either only example 1 (1:1 match) or Example 1 firm (rather concrete match). However, the formula discoveres very different matches :-(

Many thanks!

  • CF for A column:


  • CF for B column:



  • thanks a lot! one more question: what is the regex for being indiffernt about the capitalisation? – Peter the lol Jul 27 '19 at 15:31
  • @Peterthelol wrap it up into LOWER like: =REGEXMATCH(LOWER(A2),LOWER(TEXTJOIN("|",1,B$2:B))) – user0 Jul 27 '19 at 15:48
  • i am still struggling. :SS see response above – Peter the lol Aug 1 '19 at 8:41
  • @Peterthelol how so? – user0 Aug 29 '19 at 0:41

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.