How Many matched and how many non matched - Between two column

[Vishaal]

Hi,
We have data in Sheet1!A2:A and Sheet2!C2:C. In Sheet1!A2:A, there are approximately 150 entries, and in Sheet2!C2:C, there are approximately 24 entries.
We need to determine how many entries match and how many do not match between these two ranges.

 

[AhoyNC]

Maybe something like this.
The formula in F2 is an array formula and will need to be enter with CTRL-SHIFT-ENTER in Excel 2010 and 2007.
Change ranges to match your data.

Book1
	A	B	C	D	E	F
1	List1	List2		Count in Both		In Both List
2	Test1	Test2		4		Test2
3	Test2	Test4				Test4
4	Test4	Test15				Test16
5	Test5	Test16				Test24
6	Test6	Test17
7	Test7	Test24
8	Test8
9	Test10
10	Test11
11	Test12
12	Test13
13	Test14
14	Test16
15	Test22
16	Test23
17	Test24
18	Test25
19	Test26
20
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM(COUNTIF($A$2:$A$19,$B$2:$B$7))
F2	F2	=IF(ROWS($F$2:F2)>$D$2,"",INDEX($A$2:$A$19,SMALL(IF(COUNTIF($B$2:$B$7,$A$2:$A$197),MATCH(ROW($A$2:$A$19),ROW($A$2:$A$19))),ROWS($F$2:F2))))
F3:F8	F3	=IF(ROWS($F$2:F3)>$D$2,"",INDEX($A$2:$A$19,SMALL(IF(COUNTIF($B$2:$B$7,$A$2:$A$197),MATCH(ROW($A$2:$A$19),ROW($A$2:$A$19))),ROWS($F$2:F3))))
Press CTRL+SHIFT+ENTER to enter array formulas.