# Random Name picker based on 2 conditions

1. ## Random Name picker based on 2 conditions

Hi,

I am trying to create a spreadsheet that will randomly pick a name based on criterions.
I have names in Column A, Subject in Column B and KS3 0r KS4 in Column C. I want to randomly pick a name from a student from each subject and each KS. For example, pick a random student from English in KS4.
So far I have something that s the long way round and their must be a simpler way as if I continue this way then it will take some time.
I think I need to do INDEX and MATCH but I am not an expert on these and needs some guidance.
I will upload what we have done so far.  Register To Reply

2. ## Re: Random Name picker based on 2 conditions

One way:

=IFERROR(INDEX(\$A:\$A,AGGREGATE(15,6,ROW(\$A\$2:\$A\$37)/((\$B\$2:\$B\$37=\$E5)*(\$C\$2:\$C\$37=F\$4)),RANDBETWEEN(1,COUNTIFS(\$B:\$B,\$E5,\$C:\$C,F\$4)))),"")

copied across and down.  Register To Reply

3. ## Re: Random Name picker based on 2 conditions

Glen, This is amazing. It works. Thank you.

I have tried to understand what is going off in the formula as I actually need to change it around due to the fact that the data I need to check is in different columns to the one that I uploaded. But I have tried and it keeps messing up. I clearly do not know enough to be able to manipulate the formula/functions.
Please would you be able to amend the formula to do the same thing but with the columns in the order of the attachment I am attaching now.

Thanks  Register To Reply

4. ## Re: Random Name picker based on 2 conditions

=IFERROR(INDEX(\$C:\$C,AGGREGATE(15,6,ROW(\$C\$2:\$C\$37)/((\$B\$2:\$B\$37=\$H5)*(\$E\$2:\$E\$37=I\$4)),RANDBETWEEN(1,COUNTIFS(\$B:\$B,\$H5,\$E:\$E,I\$4)))),"")

will do it.

You're welcome.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 