how can compare two cells in google sheets with multiple value in every cells and if even one value in two cells are same its show me match but if in two cells we do not have any same value show me not match.

for example I have these cells in my google sheets:

• a1:[1,2,3,4]

  b1:5,6,7

  a2:[10,12,14,16]

  b2:18,14,20

for these cell it should compare a1 and b1 and print not match in c1 then for a2 and b2 print match in c2 because a2 and b2 have a same value 14

CodePudding user response：

For not matching values-

=TEXTJOIN(",",1,LAMBDA(x,y,FILTER(x,NOT(ISNUMBER(XMATCH(x,y)))))(FLATTEN(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),",")),FLATTEN(SPLIT(B1,","))))

For matching values-

=TEXTJOIN(",",1,LAMBDA(x,y,FILTER(x,ISNUMBER(XMATCH(x,y))))(FLATTEN(SPLIT(SUBSTITUTE(SUBSTITUTE(A2,"[",""),"]",""),",")),FLATTEN(SPLIT(B2,","))))

Edit: To show TRUE/FALSE try-

=INDEX(LAMBDA(x,y,OR(ISNUMBER(XMATCH(x,y))))(FLATTEN(SPLIT(SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]",""),",")),FLATTEN(SPLIT(B1,","))))

CodePudding user response：

you can try this arrayformula in Column C

=MAP(INDEX(REGEXREPLACE(A:A,"\[|\]","")),B:B,LAMBDA(ax,bx,IF(ax="",,IF(ISERROR(FILTER(SPLIT(ax,","),(MATCH(SPLIT(ax,","),SPLIT(bx,","),0)))),"FALSE","TRUE"))))

-

CodePudding user response：

Another approach, based on creating an intermediate 2D array of IFs for each pair of cells to be compared then summing the result; given the columns for comparison in A1:A & B1:B then placing this formula in C1 will spill as many results as required:

=arrayformula(iferror(map(A1:A,B1:B,lambda(a,b,if(sum(n(split(a,"[],")=flatten(split(b,"[],")))),"Match","No match")))))