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.

**Table of Contents**hide

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