Compare two columns in two different sheets and return the number of matches

[tommyk203]

I have three sheets,

• Matchsheet1 - first data set
• matchsheet2 - second data set
• matchsheet3 - results should go here

Matchsheet1 and matchshhet2 contain lists of last names, first names, date of birth.

I need match sheet 3 to compare columns A and C in matchscheet1 to columns A and C in matchsheet2, and return the number of matches of lastname and date of birth rows. The answer should be 2. as smith with a birthdate of 1/2/1950 and doe with a birthdate of 1/4/1950 are in both sheets. it doesnt matter thant in one sheet a first name is tom and the other is thomas. I only want to compare last name and date of birth

1, MatchSheet1
2, MatchSheet2
3, MatchSheet3

 

[mabbutt]

From the examples you have provided this should work:

=SUMPRODUCT((Sheet1!A2:A4=Sheet2!A2:A4)*(Sheet1!C2:C4=Sheet2!C2:C4))

 

[tommyk203]

From the examples you have provided this should work:

=SUMPRODUCT((Sheet1!A2:A4=Sheet2!A2:A4)*(Sheet1!C2:C4=Sheet2!C2:C4))

Thanks, but that doesnt work because the data is in different workbooks as I specified. I have an answer.

=sum(ArrayFormula(countif(  query(importrange(H7,"Sheet1!A2:A") & importrange(H7,"Sheet1!C2:C"),"where Col1 is not null",0),  query(importrange(H8,"Sheet1!A2:A")  & importrange(H8,"Sheet1!C2:C"),"where Col1 is not null",0))))

 

[mabbutt]

Thanks, but that doesnt work because the data is in different workbooks as I specified. I have an answer.

Glad you worked out a solution but you do not specify you would like the answer to accommodate different workbooks.

If you had then I would have provided an answer to match.

 

[mabbutt]

In the interest of other people learning here is a working solution for different workbooks:

=SUMPRODUCT(([Book1.xlsx]Sheet1!$A$2:$A$5=[Book2.xlsx]Sheet1!$A$2:$A$5)*([Book1.xlsx]Sheet1!$C$2:$C$5=[Book2.xlsx]Sheet1!$C$2:$C$5))