How to Make a Random Selection from List Without Repetition in Excel

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.

How to Make a Random Selection From List Without Repetition in Excel

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

How to Make a Random Selection From List Without Repetition in Excel

  • Now you will get the following output.

How to Make a Random Selection From List Without Repetition in Excel

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

How to Make a Random Selection From List Without Repetition in Excel

  • Now you will get the following output.

Using INDEX and MATCH Functions

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

How to Make a Random Selection From List Without Repetition in Excel

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

How to Make a Random Selection From List Without Repetition in Excel

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

How to Make a Random Selection From List Without Repetition in Excel

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

How to Make a Random Selection From List Without Repetition in Excel

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

Applying INDEX with SORTBY Function in Excel

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

Combining INDEX with RANK Function

  • Now you will get the following output.

Combining INDEX with RANK Function

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

Combining INDEX with RANK Function

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

Combining INDEX with RANK Function

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.

Combination  of INDEX and COUNTIF Functions

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

Combination  of INDEX and COUNTIF Functions

🔎 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!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo