Saquib Ahmad Shuvo
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.
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.
- Finally, you will be able to make a random selection from the list without repetition like the following.
🔎 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!
