0

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!

Improve this question

1 Answer 1

Reset to default
1

  • CF for A column:

    =REGEXMATCH(A2,TEXTJOIN("|",1,B$2:B))

  • CF for B column:

    =REGEXMATCH(B2,TEXTJOIN("|",1,A$2:A))

    0

Improve this answer
5
  • thanks a lot! one more question: what is the regex for being indiffernt about the capitalisation?
    – Peter the lol
    Jul 27, 2019 at 15:31
  • 1
    @Peterthelol wrap it up into LOWER like: =REGEXMATCH(LOWER(A2),LOWER(TEXTJOIN("|",1,B$2:B)))
    – user0
    Jul 27, 2019 at 15:48
  • i am still struggling. :SS see response above
    – Peter the lol
    Aug 1, 2019 at 8:41
  • @Peterthelol how so?
    – user0
    Aug 29, 2019 at 0:41
  • 1
    I know this is late, but thank you user0. I was looking for this solution everywhere, & even posted a question in stackexchange because I couldn't find any similar posts.
    – Genny
    Apr 4 at 5:39

Your Answer

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

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