Google-sheets – If cell y contains any word from range x:x, mark cell y green

concatenateconditional formattingformulasgoogle sheetsregex

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!

Best Answer

  • CF for A column:

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

  • CF for B column:

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

    0

Related Topic