Matching a duplicate row of numbers in a list of numbers in rows

[sgrondines]

Hi Forum,

I've been trying to figure out how to use Index/Match or ISERROR function in Excel to compare a row of selected numbers in a list of other numbers looking for an exact match. Any function in excel which would work better or simpler will do also.

I need to figure this out for a Lotto check spreadsheet that I've made which takes a set of numbers and looks for an exact match in a list of drawn numbers. I think I may need to use and array type function but it doesn't seem to work. I've attached a spreadsheet below which hopefully shows what I'm trying to do. If you look in the "Match" tab in cell H1 I'm trying a function which I hoped would look at the set of numbers A1:G1 and try to find an exact match from the "Drawn numbers" tab for any set of numbers in Rows 1 to 30 and state whether there is a matching set of numbers or not. The blank row in the "Drawn Numbers" worksheet is a bit of a test to see what would happen in the event of no numbers in a row. As you can see the function returns yes beside each row of number in the Match worksheet but those numbers don't match any rows of numbers in the Drawn numbers worksheet except for the last row which should match the last row on both worksheets. I hope this makes sense.

Thanks,

Sebastien.

[JohnTopley]

try in H! and copy down:

=IF(SUMPRODUCT(--(A1='Drawn numbers'!$A$1:$A$30),--(B1='Drawn numbers'!$B$1:$B$30),--(C1='Drawn numbers'!$C$1:$C$30),--(D1='Drawn numbers'!$D$1:$D$30),--(E1='Drawn numbers'!$E$1:$E$30),--(F1='Drawn numbers'!$F$1:$F$30),--(G1='Drawn numbers'!$G$1:$G$30)),"yes","no")

[sgrondines]

Sorry, that didn't work. Even so, if I understand correctly, it is summing up all values in each row and comparing the sums for a match, and it should post yes or no based on the match of two sums. Is that correct? If it did work there are a lot of sums which are equal but don't match up.
Thanks anyways,

Sebastien.

[Tony Valko]

Maybe this...

=SUMPRODUCT(--(MMULT(--(A1:G1='Drawn numbers'!A$1:G$30),{1;1;1;1;1;1;1})=7))

Copy down as needed.

EDIT: Ooops! I forgot half of the formula!

=IF(SUMPRODUCT(--(MMULT(--(A1:G1='Drawn numbers'!A$1:G$30),{1;1;1;1;1;1;1})=7)),"You just won millions of dollars!","Try again")

[sgrondines]

Hey, that worked! Thanks! Can you do me a favour and maybe explain what the function does? If you don't mind and have time and it's not too involved.

Sebastien.

[Tony Valko]

MMULT (matrix multiplication) is hard to explain.

Here's an explanation I wrote for a similar problem several years ago:

http://www.tech-archive.net/Archive/.../msg00758.html

See if that helps.

[JohnTopley]

This works! The last row is matched as required.

[sgrondines]

Thanks for the help. I still want to figure out how to utilize Index/match function though eventually. I think I'm not quite understanding how to use arrays. lol.

Sebastien.