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)) ), "")
anupambit1797
Nov 10, 2025Iron Contributor
Thanks Everyone, Can I request for some Legacy formula , not using LET/LAMBDA..?
Br,
Anupam
Harun24HR
Nov 11, 2025Bronze Contributor
Try this legacy formula.
=IF(COUNTIFS($A$2:$A$19,A2)*COUNTIFS($B$2:$B$19,A2)*COUNTIFS($C$2:$C$19,A2),A2,"")
- anupambit1797Nov 11, 2025Iron Contributor
Thanks Harun24HR​ , I guess we need to optimize a bit more to remove the empty/blank cells
Br,
Anupam