# Random Selection Based on Criteria in Excel (3 Cases)

Get FREE Advanced Excel Exercises with Solutions!

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. ## 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.

### 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.

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 Mahbubur Rahman

Mahbubur Rahman is a highly skilled and experienced professional with a strong background in both engineering and business administration. With an impressive technical skill set and a passion for innovation and problem-solving, Mahbubur has achieved great success in his field.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  