I have a two different list of data. In both list one column can match but the other column can be unique. How do I pull the the duplicated values with its respective partnering column? I have tried the unique, and tried match functions. I would like to only pull the same value that is duplicated in Col1 with its partnering data in Col2 list1

list2

CodePudding user response：

Use a QUERY function... Assuming you want to pull specific items?

=QUERY({Sheet1!A1:B;Sheet2!A1:B},"Select * Where Col1 = 1234")

This example will show you the results that match with 1234 in Column A

If you want all the results not specific to anything use this:

=QUERY({Sheet1!A1:B;Sheet2!A1:B},"Select * Where Col1 IS NOT NULL")

CodePudding user response：

try this if you want to list it:

=QUERY({list1!A2:B; list 2!A2:B}; 
 "where Col1 matches '"&TEXTJOIN("|"; 1; list2!A2:A)&"'")

if you want to sum it try:

=QUERY({list1!A2:B; list 2!A2:B}; 
 "select Col1,sum(Col2) 
  where Col1 matches '"&TEXTJOIN("|"; 1; list2!A2:A)&"' 
  group by Col1 
  label sum(Col2)''")