Excel: How to Randomly Select Cells Based on Criteria


Often you may want to randomly select a cell based on specific criteria in Excel.

This tutorial provides two examples of how to do so using the following dataset in Excel:

Example 1: Randomly Select Cell Based on One Criteria

We can use the following formula to randomly select one player who is on the Mavs team:

=INDEX(A2:A14,LARGE(IF(B2:B14=A17,ROW(B2:B14)-ROW(B2)+1),INT(RAND()*COUNTIF(B2:B14,A17)+1)))

This particular formula will randomly select one cell from the range A2:A14 where the corresponding cell in the range B2:B14 is equal to the value in cell A17.

We can type this formula into cell A20 and then press Ctrl + Shift + Enter:

Excel random selection with criteria

We can see that the formula randomly selected Luka, who is a player on the Mavs team.

Feel free to double click cell A20 and press Enter to run the formula again.

Each time we run the formula, it’s possible that we will get a different player on the Mavs team.

Example 2: Randomly Select Cell Based on Multiple Criteria

We can use the following formula to randomly select one player who is on the Mavs team and is a Guard:

=INDIRECT("A"&LARGE(IF($B$2:$B$14=$A$17,IF($C$2:$C$14=$B$17,ROW($A$2:$A$14),0),0), RANDBETWEEN(1,COUNTIFS(B2:B14,A17,C2:C14,B17))))

This particular formula will randomly select one cell from the range A2:A14 where the cell in range B2:B14 is equal to the value in cell A17 and the cell in range C2:C14 is equal to the value in cell B17.

We can type this formula into cell A20 and then press Ctrl + Shift + Enter:

We can see that the formula randomly selected Reggie, who is a guard on the Mavs team.

Feel free to double click cell A20 and press Enter to run the formula again.

Each time we run the formula, it’s possible that we will get a different guard on the Mavs team.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Select a Random Sample in Excel
How to Remove Duplicate Rows Based on Two Columns in Excel

Leave a Reply

Your email address will not be published.