Forum Discussion

Iron Contributor

Nov 09, 2025

Solved

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

copilot in excel

excel

Formulas and Functions

power bi

SergeiBaklan

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))
    ),
"")

duplicates_to_all_row.xlsx12 KB

m_tarler

Bronze Contributor

Nov 09, 2025

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: