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.

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

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

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

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

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

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

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

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.