Select cells from random rows if certain criteria not met

Hi there,


I'm trying to set up an excel file to get the results for my local club's weekly lotto draw. The tickets are sold annually and subscribers have the same numbers each week. They have to pick 4 numbers between 1 and 36 and if they match 4 they win the jackpot. if nobody matches all 4 numbers then anyone that matches 3 numbers wins or shares €100. if there are no match 4 or match 3 winners then 4 tickets are selected at random to win €25 each.


So far I have set it up on 2 sheets. The first contains all the subscribers' info and numbers while the second is where the numbers drawn can be inputted and the results displayed. I've managed to get the winners of match 3 and 4 to automatically display when the numbers are entered but I have no idea how to get it to select random tickets if there's no other winner. I've been trawling different sites for days now and I know as much as when I started.


I should point out that I am a novice at excel, as is anyone that might be using it in the club, and I'm just learning it as I'm going. A lot of the search results I've been getting have been pointing towards macros but, if I'm honest, I haven't got a clue about them.


I've attached what I have got so far, with personal info removed for obvious reasons. Any help would be greatly appreciated.


Thanks

Good evening,

This may not be the best solution, but it will probably work. See attached. The hiccup to this is that the winners will change each time the sheet calculates.

Best of luck,

This looks perfect. Thanks.
The only other thing that I'd like is to only display results from valid tickets in each category (match 4, match 3, or lucky dip) if this is possible.

Good morning,

Please see the attached file. I'm not very good with array formulas, so I changed a few things up. Hopefully this is what you were looking for.

Have a good day,