How do I search for a set of specific numbers within a string of numbers?

Hi folks,
I have a group of 120 sets of numbers that I always buy in my hometown lottery. I mentioned set because these 120 sets consist of 6 numbers per each set (eg. 1,2,3,4,5,6) The numbers within each set ranges from 1-49, however there are only 6 numbers in any set. The problem I'm facing is, I would like to find if there are any matches, in any of the 120 sets every time I play the lottery. It does not need to be an exact match of all 6 numbers. The reason is because you still get a win for matching 4 or 5 or 6 numbers. When I try to search in Excel, sometimes the 1 or 2 numbers in a set does not match my search query but the remaining 4 or 5 numbers does. When this happen, it shows that there is no match. Therefore, how do I search for similar numbers in a string or set, and the results would show the sets that match 6, or 5 of 4 or 3 of the numbers in my search query in any particular order i.e. without following any particular order? I buy the same 120 set of 6 numbers all the time and have these numbers saved one set per cell in an excel file.

Eg. I bought 120 sets of numbers. 5 of the sets had 3 or more matching numbers. How do I find these 5 sets out of the 120? The winning numbers are 2,6,7,23,25,34 and this would also be the string I am using to search for.

2,5,7,20,25,34 ---> 4 matches

2,6,9,23,25,34 ---> 5 matches

1,8,15,23,25,34 ---> 3 matches

2,6,7,23,25,34 ---> 6 matches

3,6,7,21,25,34 ---> 4 matches

The bold numbers are the ones that match my search query.

When I search for 2,6,7,23,25,34 which are the winning numbers, because only one set has all 6 numbers, that is the only one set that comes out. How do I get to see the other 4 sets which do not have all 6 matching numbers, but rather only 3, 4 or 5 matching numbers.

Hope someone is able to help me out. Thank you very much and best regards

Originally Posted by AlKey

Try this array formula

In B1 and copy down

Formula:

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Hi Alkey,

Thanks for the prompt response. I have tried your suggestion many times but it never seems to bring about the desired outcome. I have tried entering the formula individually into each cell, highlighting all 5 corresponding rows in the B column and then entering the formula and have also tried dragging down the formula along the B column. Unfortunately, after a lot of trial and error, I still don't get your instruction "enter array using cntrl + shift + enter". This is of course due to my incompetence with excel and its terms and jargons.

The results show the occurrence of 1, 1, 0, 1, 1 instead of 4, 5, 6, 3, 4 as in your worked example.

Thank you & best regards

Originally Posted by AlKey

Here is a modified version of the same formula that doesn't require ctrl+shift+enter.

just copy formula in B1 and drag it down

Formula:

Please Login or Register  to view this content.

Hi Alkey,

OK this time it worked.
Thank you so much.

Best regards

Originally Posted by AlKey

Here is a modified version of the same formula that doesn't require ctrl+shift+enter.

just copy formula in B1 and drag it down

Formula:

Please Login or Register  to view this content.

Hi Alkey,

OK this time it worked.
Thank you so much.

Best regards

Originally Posted by hemesh

how about

Formula:

Please Login or Register  to view this content.

example.gif

Hi Hemesh,
Thanks for the prompt response. It seems to work fine however when I populated the list, there seems to be some mistakes. I don't know why this happens but I'm sending a picture with such an example (in the example, there should be 3 matches (10, 22 and 26, instead of 4). These 'wrong counts' do not happen frequently but most of the time it just adds an additional one to the count even when sometimes there are not matches in the set.
The problem has been solved but I'm kinda curious as to why this happens.
Any ideas? It'd be good to have two different ways of achieving this - by search and by count.