Excel/Google Sheets - find matches if in 4 columns-CodePudding

I have 1 sheet with 4 columns (A,B,C,D), with each containing a list of emails. I would like a way to create a new column of emails if they were found in all 4 of columns A-D.

Does anyone know of a workable formula to accomplish this? TIA

CodePudding user response：

try:

=UNIQUE(FILTER(FLATTEN(A:D); FLATTEN(A:D)<>""))

CodePudding user response：

Here is an attempt in Excel, but the formula is too long:

=LET(range,A2:D7,
join,range&"|"&COLUMN(range),
toCol,TOCOL(join),
toColRaw,TOCOL(range),
filter,FILTER(toCol,toColRaw<>""),
unique,UNIQUE(filter),
split,SORT(TEXTSPLIT(unique,"|")),
match,MATCH(split,split)-MATCH(split,split,0),
UNIQUE(FILTER(split,match=3)))