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 Based on Criteria in Excel: 3 Cases
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)))
- 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.
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.
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)))
- After that, press the ENTER key or CTRL+SHIFT+ENTER for Excel versions lower than 2019.
- 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.
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.
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.
Download Practice Workbook
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.
<< Go Back to Random Selection in Excel | Randomize in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
