Compare two lists in excel - with some complications

[bspyke]

Hi - I have a list of all certain census tracts in one column/workbook. On another workbook I have another list of census tracts, some duplicates, that may or may not exist on the first list. I am trying to identify the ones that are on both lists. VLOOKUP/MATCH don't seem to work. Maybe because the second list has duplicates? Please help! Thanks

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]List 1[/td][td][/td][td][/td][td]List 2[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]A[/td][td]

0​

[/td][td][/td][td]B[/td][td]

1​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]B[/td][td]

1​

[/td][td][/td][td]H[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]D[/td][td]

0​

[/td][td][/td][td]I[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]G[/td][td]

0​

[/td][td][/td][td]H[/td][td]

0​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]F[/td][td]

1​

[/td][td][/td][td]F[/td][td]

1​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet6[/td][/tr][/table]

Formula in cell B2:
=COUNTIF($D$2:$D$6,A2)

Formula in cell E2:
=COUNTIF($A$2:$A$6,D2)

A number higher than zero tells you that the value exists in the other list.