Random Selection Based on Criteria in Excel (3 Cases)

Suppose we have a list of Name, Profession, Age, and Region. From the list, we want to get random Names but based on specific criteria. How should we do that? We will discuss 3 methods for random selection in Excel based on criteria in this blog post. We will use the dataset given below for the demonstration.

random selection in excel based on criteria


Download Practice Workbook


3 Cases of Random Selection Based on Criteria in Excel

In this article, we will see 3 different combination functions of INDEX, AGGREGATE, SMALL, LARGE, ROW, IF, COUNTIFS, RANDBETWEEN, and COUNTIF to select random cells based on our criteria.


Case 1: Random Selection Based on Criteria Using INDEX Function

The most used function to select random cells based on criteria is the INDEX function. We have criteria for Nevada and we want to select Names belonging to this Region randomly. This formula is a combination of INDEX, LARGE, ROW, INT, RAND, and COUNTIF functions.

Steps:

  • First, type the following formula in cell G8.
=INDEX(B5:B15,LARGE(IF(E5:E15=G5,ROW(E5:E15)ROW(E5)+1),INT(RAND()*COUNTIF(E5:E15,G5)+1)))

random selection in excel based on criteria using index function

  • At this point, press the ENTER key. If you are using Excel version 2019 or Lower the press CTRL+SHIFT+ENTER as this is an array formula. You will not find any curly braces in an upgraded version of Excel like Excel 2016, Microsoft 365, etc.

random selection in excel based on criteria using index function

So, what is happening here? Let’s go through it together.

  • IF(E5:E15=G5,ROW(E5:E15)-ROW(E5)+1) formula gives us the output as.{FALSE;FALSE;3;FALSE;FALSE;FALSE;FALSE;FALSE;9;10;FALSE}.
  • Then, the formula INT(RAND()*COUNTIF(E5:E15,G5)+1) gives us the output as 10. Here, INT(RAND() function automatically generates a random integer number.
  • As a result, =INDEX(B5:B15,10) will generate a random name Sarah, which will change every time we refresh or press ENTER in the formula bar.

Read More: Random Selection from List with No Duplicates in Excel (5 Cases)


Case 2: INDIRECT-RANDBETWEEN Combination for Random Selection

Now, we will see random selection in Excel for two criteria. We will select random names while the criteria are Actor and Alaska.

The formula below is a combination of INDIRECT, LARGE, IF, RANDBETWEEN, and ROW functions.

Steps:

  • First, type the following formula in cell G9.
=INDEX(B5:B15,LARGE(IF(E5:E15=G5,ROW(E5:E15)ROW(E5)+1),INT(RAND()*COUNTIF(E5:E15,G5)+1)))

random selection in excel based on criteria with indirect function

  • After that, press the ENTER key or CTRL+SHIFT+ENTER for Excel versions lower than 2019.

random selection in excel based on two criteria

  • Here, “B” in the formula indicates the Name column.
  • Formula RANDBETWEEN(1,COUNTIFS(C5:C15,G6,E5:E15,G5)) returns us a random value 2.{0;0;0;8;0;0;0;0;0;0;15} is the value we get from the formula IF($C$5:$C$15=$G$6,IF($E$5:$E$15=$G$5,ROW($B$5:$B$15),0),0).
  • Ultimately, =INDIRECT(“B”&15) gives us the final result as Kate.

Read More: How to Make a Random Selection from List Without Repetition in Excel


Similar Readings


Case 3: AGGREGATE Function for Random Selection Based on Criteria

In our last method, we will have a look at the AGGREGATE function.

Steps:

  • First, type the following formula in cell G10.
=INDEX(B4:B15,AGGREGATE(15,3,(E5:E15<>"Nevada")*ROW(E5:E15), RANDBETWEEN(COUNTIF(E5:E15,"Nevada")+1,COUNTA(E5:E15))))

  • At last, press the ENTER key. If you are using Excel version 2019 or Lower the press CTRL+SHIFT+ENTER as this is an array formula.

  • RANDBETWEEN(COUNTIF(E5:E15,”Nevada”)+1,COUNTA(E5:E15)) formula gives us a output 5 and AGGREGATE(15,3,(E5:E15<>”Nevada”)*ROW(E5:E15),RANDBETWEEN(COUNTIF(E5:E15,”Nevada”)+1,COUNTA(E5:E15))) yield the output as 11, which is clearly the row number of Timothy (Count from the Header column).
  • As a result, =INDEX(B4:B15,11) will give the result as Timothy.

Read More: How to Freeze Random Selection in Excel


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.


Conclusion

That’s all for the article. These are 3 different methods for random selection in Excel based on criteria. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo