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
- Nov 11, 2025
Couple of more with similar logic from here How to find common values in 3 columns in Excel? and here Finding common values in 3 columns | MrExcel Message Board
=IFNA( INDEX( $A$2:$A$19, MATCH(0, COUNTIF($H$1:H1, $A$2:$A$19) + IF( IF( COUNTIF($B$2:$B$19, $A$2:$A$19)>0,1,0) + IF(COUNTIF($C$2:$C$8, $A$2:$A$19)>0,1,0)=2,0,1 ), 0 ) ), "" )and
=IFERROR( INDEX( $A$1:$A$19, SMALL( IF( ISNUMBER( MATCH( IF( ISNUMBER( MATCH($A$1:$A$19,$B$1:$B$19,0)),$A$1:$A$19), $C$1:$C$8, 0 ) ), ROW($A$1:$A$19) ), ROWS(E$1:E1)) ), "")
JohnVergaraD
Nov 11, 2025Copper Contributor
Hi everyone!
One option with "Legacy Formula" could be this:
=IFERROR(AGGREGATE(15,6,A$2:A$19/(MMULT(COUNTIF(OFFSET(A$2:A$19,,{1,2}),A$2:A$19),{1;1})=2),ROWS(E$2:E2)),"")This formula assumes that the numbers in each column are unique, just like in the example. Blessings!