I have a Google Sheets formula that finds the common items between two comma-separated values but I'm looking for the missing values. How do I turn this around?

For example:

C2: 3|5|6|8 D2: 8|7|3|6

The formula below will output: 3|6|8 but I want: 5|7

=TEXTJOIN("|",TRUE,filter(trim(split(C2,"|")),regexmatch(trim(split(C2,"|")),textjoin("|",true,trim(split(D2,"|"))))))

TIA

CodePudding user response：

Please try:

=LAMBDA(y,z,TEXTJOIN("|",1,{IFNA(FILTER(y,ISERROR(XMATCH(y,z)))),IFNA(FILTER(z,ISERROR(XMATCH(z,y))))}))(IFERROR(SPLIT(C2,"|")),IFERROR(SPLIT(D2,"|")))