Count matches between two arrays

How can I count the number of entries two arrays have in common, using just standard excel formulae (i.e. no VBA)?

Can't find an answer anywhere.

e.g.

      A   B
 +-------------
1|    1   10
2|    2   11
3|    3   12
4|    4   13
5|    5   14
6|    6   1
7|    7   3
8|    8   17

What function can I give A1:A7 and B1:B7 as arguments to, and get 2 as a result back (because they both have 1 and 3 in common).

will

Posted 2015-09-03T16:17:31.513

Reputation: 131

What result would you expect if a = [1 2 3 4 5] and b = [10 11 3 3 15] Is that one match or two? – Adam – 2015-09-03T16:30:36.363

for the data i have each column has only distinct values, so it doesn't matter for me. – will – 2015-09-03T16:32:17.627

Answers