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-

`=INDEX(B5:B12,RANDBETWEEN(1,8))`

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

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

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

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

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

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

**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-

`=RAND()`

- 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**–

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

- Then just press the
**ENTER**button to finish.

**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 **INDEX function**Â 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.

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

- 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**–

`=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.

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**–

`=XLOOKUP(F5:F7,D5:D12,B5:B12)`

- 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**