If you are looking for some special tricks to make a random selection from a list without repetition in Excel, you’ve come to the right place. In Microsoft Excel, there are numerous ways to make a **random selection from a list without repetition**. In this article, we’ll discuss five methods to make a random selection from a list without repetition. Let’s follow the complete guide to learn all of this.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 4 Methods to Make a Random Selection from List Without Repetition in Excel

We will use four effective and tricky methods to make a random selection in Excel in the following section. This section provides extensive details on four methods. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge.

### 1. Using INDEX and MATCH Functions

To make a random selection from the list without repetition, we will combine **INDEX** and **MATCH **functions. Here, the **INDEX **function returns a value or reference of the cell at the intersection of a particular row and column in a given range. And the **MATCH **function returns the relative position of an item in an array that matches a specified value in a specified order. Here, we also use the **RAND **and **RANK **functions. The **RAND **function returns a random number greater than or equal to 0 or less than to. The **RANK function **returns the rank of a number in a list of numbers. You have to follow the following steps to make a **random selection from the list** in the below dataset.

**📌 Steps:**

- We will use the following formula in the cell
**C5:**

`=RAND()`

Here, this formula returns a random number greater than or equal to 0 or less than 1.

- Press
**Enter**and drag the**Fill handle**icon**.**

- Now you will get the following output.

- Next,we will use the following formula in the cell
**D5:**

`=RANK(C5,$C$5:$C$14)`

Here, this formula returns the rank of a number in the list of numbers.

- Press
**Enter**and drag the**Fill handle**icon.

- Now you will get the following output.

- Finally, we will use the following combined formula in the cell
**G5:**

`=INDEX($B$5:$D$14,MATCH(F5,$D$5:$D$14,0),1)`

- Press
**Enter**and drag the**Fill handle**icon**.**

- Finally, you will be able to make a random selection from the list without repetition like the following.

- Now, no matter how many times you refresh the page, you will never get the same name twice.

**🔎 How Does the Formula Work? **

- Here,
**MATCH(F5,$D$5:$D$14,0)**returns the relative position of an item in the array that matches a specified value in a specified order. - Finally, the
**INDEX**function returns a value or reference of the cell at the intersection of a particular row and column in the given range.

**Read More:**** How to Randomly Select Rows in Excel (2 Ways)**

### 2. Applying INDEX with SORTBY Function in Excel

To make a random selection from the list without repetition, we will combine the **INDEX **and **SORTBY **functions**. **Here, the **SORTBY **function sorts a range or array based on the values in a corresponding range or array. Here, We also use the **RANDARRAY, ****ROWS**, and **SEQUENCE **functions. You have to follow the following steps to make a random selection from the list in the below dataset.

**📌 Steps:**

- We will use the following formula in the cell
**C5:**

`=RANDARRAY(ROWS(B5:B14))`

- Here, this formula returns an array of random numbers like the following.

- Next, we will use the following formula in the cell
**E9:**

`=SEQUENCE(F5)`

- Here, this formula returns a sequence of numbers in the range of cells
**E9:E11**.

- Next, we will use the following formula in the cell
**F9:**

`=INDEX(SORTBY($B$5:$B$14,C5#),E9)`

- Press
**Enter**and drag the**Fill handle**icon**.**

- Finally, you will be able to make a random selection from the list without repetition like the following.

**🔎 How Does the Formula Work? **

- Here,
**SORTBY($B$5:$B$14,C5#)**sorts a range or array based on the values in the corresponding range or array. - Finally, the
**INDEX**function returns a value or reference of the cell at the intersection of a particular row and column in the given range.

**Read More:** **How to Generate a Random String from a List in Excel (5 Suitable Ways)**

### 3. Combining INDEX with RANK Function

To make a random selection from the list without repetition, we will combine the **INDEX ** and **RANK **functions**. **Here, we also use the **RAND ** function. You have to follow the following steps to make a random selection from the list in the below dataset.

**📌 Steps:**

- We will use the following formula in the cell
**C5:**

`=RAND()`

Here, this formula returns a random number greater than or equal to 0 or less than 1.

- Press
**Enter**and drag the**Fill handle**icon**.**

- Now you will get the following output.

- Next, we will use the following formula in the cell
**D5:**

`=RANK(C5,$C$5:$C$14)`

Here, this formula returns the rank of a number in the list of numbers.

- Press
**Enter**and drag the**Fill handle**icon**.**

- Now you will get the following output.

- Finally, we will use the following combined formula in the cell
**G5:**

`=INDEX($B$5:$B$14,D5:D14)`

Here, this formula returns a value or reference of the cell at the intersection of a particular row and column in the given range.

- Press
**Enter**and drag the**Fill handle**icon**.** - Finally, you will be able to make a random selection from the list without repetition like the following.

**Read More: Random Selection Based on Criteria in Excel (3 Cases)**

### 4. Combination of INDEX and COUNTIF Functions

To make a random selection from the list without repetition, we will combine** INDEX **and** COUNTIF** functions**. **Here, we also use the **RAND **function**. **You have to follow the following steps to make a random selection from the list in the below dataset.

**📌 Steps:**

- We will use the following formula in the cell
**C5:**

`=RAND()`

Here, this formula returns a random number greater than or equal to 0 or less than 1.

- Press
**Enter**and drag the**Fill handle**icon**.**

- Now you will get the following output.

- Next, we will use the following formula in the cell
**F9:**

`=INDEX($B$5:$B$14,RANK.EQ(C5,$C$5:$C$14)+COUNTIF($C$5:C5,C5)-1)`

- Press
**Enter**and drag the**Fill handle**icon**.**

**🔎 How Does the Formula Work? **

- Here,
**COUNTIF($C$5:C5, C5)**counts the number of cells within a range that meet the above condition. - Next,
**EQ(C5, $C$5:$C$14)**returns the rank of a number in the list of numbers. - Finally, the
**INDEX**function returns a value or reference of the cell at the intersection of a particular row and column in the given range.

**Read More:**** How to Freeze Random Selection in Excel**

## Conclusion

That’s the end of today’s session. I strongly believe that from now you may make a random selection from a list without repetition in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!