Total number of matching numbers from 2 arrays

[williamspage]

Hi All,

I run a weekly competition where each person chooses 10 numbers, and then each national lotto 6 numbers are drawn out. I mark off the number that people have chosen against the drawn lotto numbers. Once one person has all their numbers drawn out they win the money.

I have a sheet with names down Column A (Rows 1 - 16) and their chosen number in Columns B - K

Underneath this table I have the drawn out lotto numbers Columns A - F Rows 17 - 32)

I would like a formula that works out how many numbers are matched for each person as I input the lotto numbers in the A - F Row 17 - 32 and output this figure in Column L (against their name).

Please help

[Fotis1991]

A sample workbook pls?

[williamspage]

File attached as requested :-)

[Fotis1991]

An unusual but simple approach.

=IF(COUNTIF($A$20:$F$23;B1)>0;1;0)

=SUM(L1:U1)

[williamspage]

Thanks for your help :-)

[Tony Valko]

=IF(COUNTIF($A$20:$F$23;B1)>0;1;0)

Or, more simply:

=--(COUNTIF($A$20:$F$23;B1)>0)

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.