How to display only matches from two lists with Multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1316346%22%20slang%3D%22en-US%22%3EHow%20to%20display%20only%20matches%20from%20two%20lists%20with%20Multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1316346%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20sheet%20titled%20List%201%20and%20and%20List%202.%20I%20have%20a%203rd%20sheet%20titled%20%22Matches%22.%20I%20want%20to%20only%20have%20the%20rows%20that%20match%20between%20the%20two%20List%20sheets%20populate%20in%20the%20%22Matches%22%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20i%20go%20about%20this%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1316346%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1317662%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20display%20only%20matches%20from%20two%20lists%20with%20Multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1317662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20the%20easiest%20way%20is%20with%20Power%20Query%20-%20add%20named%20ranges%20for%20List1%20and%20List%202%2C%20query%20both%2C%20merge%20with%20inner%20join%20and%20return%20result%20back.%20Script%20is%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%2C%0A%0A%20%20%20%20GetList1%20%3D%20Table.SelectRows(Source%2C%20each%20(%5BName%5D%20%3D%20%22List1%22))%7B0%7D%5BContent%5D%2C%0A%20%20%20%20List1Headers%20%3D%20Table.PromoteHeaders(GetList1%2C%20%5BPromoteAllScalars%3Dtrue%5D)%2C%0A%20%20%20%20List1Clean%20%3D%20Table.SelectRows(List1Headers%2C%20each%20(%5BNames%5D%20%26lt%3B%26gt%3B%20null))%2C%0A%0A%20%20%20%20GetList2%20%3D%20Table.SelectRows(Source%2C%20each%20(%5BName%5D%20%3D%20%22List2%22))%7B0%7D%5BContent%5D%2C%0A%20%20%20%20List2Headers%20%3D%20Table.PromoteHeaders(GetList2%2C%20%5BPromoteAllScalars%3Dtrue%5D)%2C%0A%20%20%20%20List2Clean%20%3D%20Table.SelectRows(List2Headers%2C%20each%20(%5BNames%5D%20%26lt%3B%26gt%3B%20null))%2C%0A%0A%20%20%20%20MatchesFromBoth%20%3D%20Table.NestedJoin(%0A%20%20%20%20%20%20%20%20List1Clean%2C%20%7B%22Names%22%2C%20%22DOB%22%2C%20%22Product%22%7D%2C%0A%20%20%20%20%20%20%20%20List2Clean%2C%20%7B%22Names%22%2C%20%22DOB%22%2C%20%22Product%22%7D%2C%0A%20%20%20%20%20%20%20%20%22FromList2%22%2C%20JoinKind.Inner%0A%20%20%20%20)%2C%0A%20%20%20%20RemoveUnused%20%3D%20Table.RemoveColumns(MatchesFromBoth%2C%7B%22FromList2%22%7D)%0Ain%0A%20%20%20%20RemoveUnused%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321825%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20display%20only%20matches%20from%20two%20lists%20with%20Multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321825%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20completely%20unfamiliar%20with%20Power%20Queries%2C%20is%20there%20any%20way%20to%20resolve%20this%20through%20a%20formula%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324685%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20display%20only%20matches%20from%20two%20lists%20with%20Multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20are%20couple%20of%20more%20variants%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20918px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185580i80373ED28CB25366%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20on%20Excel%20with%20dynamic%20arrays%2C%20when%20in%20E1%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER('List%201'!%24A%241%3A%24C%248%2C%0A%20%20%20%20COUNTIF('List%201'!%24A%241%3A%24A%248%2C'List%202'!%24A%241%3A%24A%248)*%0A%20%20%20%20COUNTIF('List%201'!%24B%241%3A%24B%248%2C'List%202'!%24B%241%3A%24B%248)*%0A%20%20%20%20COUNTIF('List%201'!%24C%241%3A%24C%248%2C'List%202'!%24C%241%3A%24C%248)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20not%2C%20in%20I2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(%0A%20%20INDEX('List%201'!A%242%3AA%248%2C%0A%20%20%20AGGREGATE(15%2C6%2C%0A%20%20%20%20%20%201%2F(COUNTIF(%20'List%202'!%24A%242%3A%24A%248%2C'List%201'!%24A%242%3A%24A%248)%26lt%3B%26gt%3B0)%2F%0A%20%20%20%20%20%20%20%20%20%20(COUNTIF('List%202'!%24B%242%3A%24B%248%2C'List%201'!%24B%242%3A%24B%248)%26lt%3B%26gt%3B0)%2F%0A%20%20%20%20%20%20%20%20%20%20(COUNTIF('List%202'!%24C%242%3A%24C%248%2C'List%201'!%24C%242%3A%24C%248)%26lt%3B%26gt%3B0)*%0A%20%20%20%20%20%20(ROW('List%201'!%24A%242%3A%24A%248)-ROW('List%201'!%24A%241))%2C%0A%20%20%20%20%20ROW()-ROW(%24A%241)%0A%20%20%20%20))%2C%0A%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20drag%20it%20to%20the%20right%20and%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@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

 

Highlighted

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

Highlighted

@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