How to Randomly Select from a List in Excel (5 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.

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

1. Using Excel 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-

• Then just hit the ENTER button and you will get a random value like the image below.

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

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–

• Finally, just press the ENTER button for the output.

Then you see, we got another random value.

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-

• Later, to get the result, just press the ENTER button.

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–

• Hit the ENTER button to finish.

3. Combining 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-

• Press the ENTER button.

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

We got random values for each corresponding name.

• After, that, type the following formula in Cell E5–

• Then just press the ENTER button to finish.

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

Now see, we got three random values.

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–

• To get the output, just hit ENTER button.

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

The random ranks are placed successfully.

• At this moment, insert the final formula in Cell G5–

• Hit the ENTER button to get the first random value.

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

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

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

To get random multiple values without 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–

• Finally, just press the ENTER button to get the array.

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.

Download Practice Workbook

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

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.

<< Go Back to Random Selection in Excel | Randomize in Excel | Learn Excel