Mahbubur Rahman
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.
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)))
- 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.
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)))
- 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.
Read More: How to Make a Random Selection from List Without Repetition in Excel
Similar Readings
- How to Generate a Random String from a List in Excel (5 Suitable Ways)
- Excel VBA: Random Selection from List (3 Examples)
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.
