excel - Comparing comma separated numbers in cells

Question

I am trying to compare the numbers in the Reachability Set column with the numbers in the same row of the Antecedent Set column and return the common values in the corresponding cells of the Intersection Set column.

Screenshot:

Answer 1

In Excel 2016 (but NOT Excel 2013), you can use the following array-entered formula.

=TEXTJOIN(",",TRUE,IFERROR(1/(1/(ISNUMBER(FIND(","&TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99))&",",","&A2&","))))*TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99)),""))

seq_99 is a Named Formula

Refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

To enter an array formula, after entering the formula in the cell, confirm by holding down ctrl + shift while hitting enter. If you do it correctly, Excel will place braces {...} around the formula.