How to Randomly Select from a List in Excel (5 Swift Methods)

In some cases like the lottery, and team selection we need to select random values. Instead of doing it manually, we can do it in Excel more quickly and smartly just by using some functions. In this article, we’ll show 5 swift methods to randomly select from a list in Excel with sharp illustrations.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


5 Ways to Randomly Select from a List in Excel

Let’s get introduced to our dataset which represents some participants in a lottery competition.


1. Using INDEX and RANDBETWEEN Functions to Select from a List Randomly

First, we’ll show how to use the INDEX and RANDBETWEEN functions to select randomly from a list of values. While applying these functions, we’ll need to set the range of the list and that task we can do in three ways. Let’s go forward to explore it.


1.1. Inserting List Range Manually

The first easy way is to insert the range manually within the RANDBETWEEN function. We have 8 values in our list so the range will be 1 to 8.

Steps:

  • In Cell D5, insert the following formula-
=INDEX(B5:B12,RANDBETWEEN(1,8))
  • Then just hit the ENTER button and you will get a random value like the image below.

Using INDEX and RANDBETWEEN Functions to Select from a List Randomly

Formula Breakdown:

  • RANDBETWEEN(1,8)

It will return a random number among the numbers from 1 to 8.

  • INDEX(B5:B12,RANDBETWEEN(1,8))

Then the INDEX function returns the corresponding value from the list according to the number.

  • Whenever we press Calculate Now from the Formulas tab, it will return a new random value every time.

Using INDEX and RANDBETWEEN Functions to Select from a List Randomly


1.2. Inserting List Range Using ROWS Function

Another tricky way is to use the ROWS function to get the max range of the list. As the minimum value is always 1 for any list, so we’ll always insert 1 manually.

Steps:

  • Type the following formula in Cell D5
=INDEX(B5:B12,RANDBETWEEN(1,ROWS(B5:B12)))
  • Finally, just press the ENTER button for the output.

Then you see, we got another random value.

Using INDEX and RANDBETWEEN Functions to Select from a List Randomly

Formula Breakdown:

  • ROWS(B5:B12)

It will return the total number of rows that used the list and actually that expresses the maximum number of the list.

  • RANDBETWEEN(1,ROWS(B5:B12))

Later, the RANDBETWEEN function will return a random number within the range.

  • INDEX(B5:B12,RANDBETWEEN(1,ROWS(B5:B12)))

Finally, the INDEX function will extract the value according to the number.


1.3. Inserting List Range Using COUNTA Function

Instead of the rows function, we can also use the COUNTA function too, which will return the same output. Because the number of rows and cells in a column within the rows is the same eventually.

Steps:

  • In Cell D5, write the following formula-
=INDEX(B5:B12,RANDBETWEEN(1,COUNTA(B5:B12)))
  • Later, to get the result, just press the ENTER button.

Formula Breakdown:

  • This formula works as the previous formula, just using the COUNTA function instead of the ROWS function to return the highest range as the number of rows and cells in a column within the rows is the same eventually.

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


2. Using Excel CHOOSE and RANDBETWEEN Functions to Select from a List Randomly

If we use the CHOOSE function instead of the INDEX function then also we can select a random value quickly from a list. But this formula is quite longer than the other methods, the formula will increase proportionally according to the size of your list.

Steps:

  • Insert the following formula in Cell D5
=CHOOSE(RANDBETWEEN(1,8),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10,$B$11,$B$12)
  • Hit the ENTER button to finish.

Using Excel CHOOSE and RANDBETWEEN Functions to Select from a List Randomly

Formula Breakdown:

  • RANDBETWEEN(1,8)

First, it will return a random number from 1 to 8.

  • CHOOSE(RANDBETWEEN(1,8),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10,$B$11,$B$12)

Finally, the CHOOSE function will return the value from the range of the list according to the number.

Read More: How to Select Random Sample in Excel (4 Methods)


3. Using INDEX, RAND, and RANK Functions to Randomly Select Without Duplicates

If you want to select multiple values from a list then you will have to use the INDEX, RAND, and RANK functions. Here, I’ll return three random values and for that will need a helper column.

Steps:

  • First, insert a helper column beside the list column.
  • Next, insert the following formula to generate some random values-
=RAND()
  • Press the ENTER button.

Using INDEX, RAND, and RANK Functions to Randomly Select Without Duplicates

  • Later, drag down the Fill Handle icon to copy the formula for the other cells.

Using INDEX, RAND, and RANK Functions to Randomly Select Without Duplicates

We got random values for each corresponding name.

  • After, that, type the following formula in Cell E5
=INDEX($B$5:$B$12,RANK(C5,$C$5:$C$12))
  • Then just press the ENTER button to finish.

Using INDEX, RAND, and RANK Functions to Randomly Select Without Duplicates

Formula Breakdown:

  • RANK(C5,$C$5:$C$12)

The RANK function will create random rank numbers for every value of the range C5:C12.

  • INDEX($B$5:$B$12,RANK(C5,$C$5:$C$12))

After that, the INDEXfunction will extract the value according to the number.

  • Finally, use the Fill Handle tool to copy the formula and get the other two random values.

Now see, we got three random values.

Using INDEX, RAND, and RANK Functions to Randomly Select Without Duplicates

Read More: Excel VBA: Random Selection from List (3 Examples)


4. Randomly Select Multiple Unique Values Using INDEX, RAND, RANK, and MATCH Functions

The previous method can generate duplicate values. To get rid of this thing, we’ll have to use the MATCH function with the functions of the previous method. For that, we’ll need another helper column where we’ll generate the random rank numbers and will need another column to insert the serial numbers for the three winners.

Steps:

  • Firstly, insert the new helper columns.
  • Later, write the following formula in Cell D5
=RANK(C5,$C$5:$C$12)
  • To get the output, just hit ENTER button.

Randomly Select Multiple Unique Values Using INDEX, RAND, RANK, and MATCH Functions

  • Next, use the Fill Handle tool to copy the formula.

Randomly Select Multiple Unique Values Using INDEX, RAND, RANK, and MATCH Functions

The random ranks are placed successfully.

  • At this moment, insert the final formula in Cell G5
=INDEX($B$5:$D$12,MATCH(F5,$D$5:$D$12,0),1)
  • Hit the ENTER button to get the first random value.

Formula Breakdown:

  • MATCH(F5,$D$5:$D$12,0)

The MATCH function will return the relative position from the array according to the specific value.

  • INDEX($B$5:$D$12,MATCH(F5,$D$5:$D$12,0),1)

Next, the INDEX function will extract the corresponding value according to the relative position.

  • Lastly, use the Fill Handle tool to get the other two random values.

Randomly Select Multiple Unique Values Using INDEX, RAND, RANK, and MATCH Functions

See, we got three different random names. Each time you will recalculate, you will get new names.

Randomly Select Multiple Unique Values Using INDEX, RAND, RANK, and MATCH Functions

Read More: How to Randomly Select a Winner in Excel (2 Easy Ways)


5. Using XLOOKUP Function to Randomly Select Multiple Values Without Duplicates in Excel

To get random multiple values avoiding duplicates, there’s another easy way. Only the XLOOKUP function can perform the same previous operation, just we’ll have to use it as an array formula.

Steps:

  • Use the formula given below in Cell G5
=XLOOKUP(F5:F7,D5:D12,B5:B12)
  • Finally, just press the ENTER button to get the array.

Using XLOOKUP Function to Randomly Select Multiple Values Without Duplicates in Excel

Here are our random names as an array output. The array formula will save time because no need to use the Fill Handle tool here.

Read More: Random Selection from List with No Duplicates in Excel (5 Cases)


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to randomly select from a list in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo