Home > front end >  Compare a cell with a column and returns True if they contain at least a common character in GOOGLE
Compare a cell with a column and returns True if they contain at least a common character in GOOGLE

Time:12-01

I would like it to return true in C1 if A1 and B1 have at least one common character and return false in C2 if A2 and B1 do not have any common character

A B C
1 [1, 7, 12] 1, 8, 9, 40
2 [10, 15, 22]
3 [1, 15, 121, 10]
=INDEX(REGEXMATCH(A1:A3, "\b"&SUBSTITUTE(B1:B3, ",", "|")&"\b"))

CodePudding user response:

Remove spaces and use a capturing group, like this:

=arrayformula( 
  regexmatch( 
    A1:A3, 
    "\b(" & regexreplace( trim(B1), "\s*,\s*", "|" ) & ")\b" 
  ) 
)

CodePudding user response:

use:

=INDEX(REGEXMATCH(A1:A3, "\b("&SUBSTITUTE(B1, ",", "|")&")\b"))

enter image description here

  • Related