How to display only matches from two lists with Multiple columns

Brass Contributor

I have a sheet titled List 1 and and List 2. I have a 3rd sheet titled "Matches". I want to only have the rows that match between the two List sheets populate in the "Matches" sheet. 

 

How would i go about this? 

3 Replies

@shade206 

Perhaps the easiest way is with Power Query - add named ranges for List1 and List 2, query both, merge with inner join and return result back. Script is like

let
    Source = Excel.CurrentWorkbook(),

    GetList1 = Table.SelectRows(Source, each ([Name] = "List1")){0}[Content],
    List1Headers = Table.PromoteHeaders(GetList1, [PromoteAllScalars=true]),
    List1Clean = Table.SelectRows(List1Headers, each ([Names] <> null)),

    GetList2 = Table.SelectRows(Source, each ([Name] = "List2")){0}[Content],
    List2Headers = Table.PromoteHeaders(GetList2, [PromoteAllScalars=true]),
    List2Clean = Table.SelectRows(List2Headers, each ([Names] <> null)),

    MatchesFromBoth = Table.NestedJoin(
        List1Clean, {"Names", "DOB", "Product"},
        List2Clean, {"Names", "DOB", "Product"},
        "FromList2", JoinKind.Inner
    ),
    RemoveUnused = Table.RemoveColumns(MatchesFromBoth,{"FromList2"})
in
    RemoveUnused

 

I'm completely unfamiliar with Power Queries, is there any way to resolve this through a formula? @Sergei Baklan 

@shade206 

Here are couple of more variants

image.png

If you are on Excel with dynamic arrays, when in E1

 

=FILTER('List 1'!$A$1:$C$8,
    COUNTIF('List 1'!$A$1:$A$8,'List 2'!$A$1:$A$8)*
    COUNTIF('List 1'!$B$1:$B$8,'List 2'!$B$1:$B$8)*
    COUNTIF('List 1'!$C$1:$C$8,'List 2'!$C$1:$C$8)
)

 

If not, in I2

 

=IFERROR(
  INDEX('List 1'!A$2:A$8,
   AGGREGATE(15,6,
      1/(COUNTIF( 'List 2'!$A$2:$A$8,'List 1'!$A$2:$A$8)<>0)/
          (COUNTIF('List 2'!$B$2:$B$8,'List 1'!$B$2:$B$8)<>0)/
          (COUNTIF('List 2'!$C$2:$C$8,'List 1'!$C$2:$C$8)<>0)*
      (ROW('List 1'!$A$2:$A$8)-ROW('List 1'!$A$1)),
     ROW()-ROW($A$1)
    )),
"")

 

and drag it to the right and down