Comparison of array for Matching and non matching values

Are you still using Excel 2010? This is easy with Office 365...

Please update your profile NOW to show O365.

One cell (A17) causes problems... The comma "isn't there". It is seen as a thousands separator, not as a comma in a text string. We might need a workaround for that!! I formatted the cell as TEXT and reintroduced the comma.

However, B2:

=LET(a,FILTERXML("<A><B>"&SUBSTITUTE(A2,",","</B><B>")&"</B></A>","//B"),TEXTJOIN(", ",TRUE,IF(ISNUMBER(MATCH(a,FILTERXML("<A><B>"&SUBSTITUTE($G$1,",","</B><B>")&"</B></A>","//B"),0)),a,"")))

and C2:

=LET(a,FILTERXML("<A><B>"&SUBSTITUTE(A2,",","</B><B>")&"</B></A>","//B"),TEXTJOIN(", ",TRUE,IF(ISNA(MATCH(a,FILTERXML("<A><B>"&SUBSTITUTE($G$1,",","</B><B>")&"</B></A>","//B"),0)),a,"")))

Here's how.

Please try at

B2
=LET(a,FILTERXML("<x><m>"&SUBSTITUTE(TEXT(A2,"#,#"),",","</m><m>")&"</m></x>","//m"),TEXTJOIN(",",,FILTER(a,ISNUMBER(FIND(","&a&",",","&G$1&",")),"")))

C2
=LET(a,FILTERXML("<x><m>"&SUBSTITUTE(TEXT(A2,"#,#"),",","</m><m>")&"</m></x>","//m"),TEXTJOIN(",",,FILTER(a,ISERR(FIND(","&a&",",","&G$1&",")),"")))