Enhanced lottery question

[Brad]

The rules of the game are this

Pick 6 numbers and you must keep this six numbers until someone wins.
assume you pick 1, 2, 3, 4, 7, 8

Assume that the lottery numbers in week 1 are
1, 2, 3, 4, 5, 6

You need numbers 7, 8 to win

Assume that the lottery numbers in week 2 are
1, 7, 8, 10, 15, 25

I would like the system to know that you got all 6 numbers correct and won
the game. (The 1 in week two should be ignored - since it was included as a
winning number in week one.)

Any ideas??




In week 1 - assume that you get 4 of the numbers correct - but you are
missing

 

[Bernie Deitrick]

Brad,

Every week, enter the picked numbers down column A, starting in row 2 (after the first week, enter
the six numbers starting in row 8, etc). Enter the players' names in column B starting in row 2,
and their 6 selected numbers into the same row, in columns C:H. In cell I2 enter the formula

=IF(SUMPRODUCT(ISNUMBER(MATCH(C2:H2,A:A,FALSE))*1)=6,"Winner!","")

and copy down to match your list of players.

HTH,
Bernie
MS Excel MVP

 

[Brad]

That works, but please enlighten me

Why does this work using "a:a", but not using example "L7:Q15"?

I'm taking that the false = match_type zero, which makes sense.

I really want to know why it works as well as that it does work......

Thanks again.....

 

[Bernie Deitrick]

BRAD,

The array in MATCH needs to be one-dimensional - either a single row or a single column, but not
more than 1 of each... You would need to have six MATCH formulas to work with six columns of
numbers.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

BRAD,

The array in MATCH needs to be one-dimensional - either a single row or a single column, but not
more than 1 of each... You would need to have six MATCH formulas to work with six columns of
numbers.

HTH,
Bernie
MS Excel MVP

 

[Brad]

Thank you,

I'm still amazed what Excel can do, you just need to know how to tell it.

I appreciate your help.....