Forum Discussion
anupambit1797
Nov 09, 2025Iron Contributor
Common Values in 3 Columns
Dear Experts, I have a data like below, in Col-A/B/C and in Col-E , I want the common rnti's from A&B&C Col, Thanks in Advance, Br, Anupam
m_tarler
Nov 09, 2025Bronze Contributor
You could use a number of SCAN or REDUCE type of LAMBDA functions to loop through each list but here is what I think is a little more general option. I also assumed that each list could have duplicates themselves:
=LET(in_1, O1:O15, in_2, P1:P15, in_3, Q1:Q15,
ustack, VSTACK(UNIQUE(in_1),UNIQUE(in_2),UNIQUE(in_3)),
Duplicates, LAMBDA(array,[number],
LET(num, IF(ISOMITTED(number),1,number),
counts, MMULT(--(array=TRANSPOSE(array)),SEQUENCE(ROWS(array),,1,0)),
UNIQUE(FILTER(array,(array<>"")*(counts>=num),"no dups"))
)),
Duplicates(ustack,3)
)and you just need to have row 1 and row 2 match the actual input columns (both in range reference and number of input columns) and then on row 8 that number 3 matches the number of columns.
Here it is in action: