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

Our sample dataset represents participants in a lottery. We’ll randomly select a winner.

## Method 1 – Using the INDEX and RANDBETWEEN Functions to Select from a List Randomly

### Case 1.1 – Inserting the List Range Manually

Steps:

• In Cell D5, insert the following formula:
`=INDEX(B5:B12,RANDBETWEEN(1,8))`
• Hit Enter, 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))

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.

### Case 1.2 – Inserting the List Range Using the ROWS Function

Steps:

• Use the following formula in Cell D5:
`=INDEX(B5:B12,RANDBETWEEN(1,ROWS(B5:B12)))`
• Hit Enter.

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

The RANDBETWEEN function will return a random number within the range.

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

The INDEX function will extract the value according to the number.

### Case 1.3 – Inserting the List Range Using the COUNTA Function

Steps:

• In Cell D5, insert the following formula:
`=INDEX(B5:B12,RANDBETWEEN(1,COUNTA(B5:B12)))`
• Hit Enter.

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.

## Method 2 – Using Excel CHOOSE and RANDBETWEEN Functions to Select from a List Randomly

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.

Formula Breakdown:

• RANDBETWEEN(1,8)

This 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)

The CHOOSE function will return the value from the range of the list according to the random number.

## Method 3 – Combining INDEX, RAND, and RANK Functions to Randomly Select Multiple Values

Steps:

• Insert a helper column next to the list column.
• Insert the following formula to generate some random values in the new column:
`=RAND()`
• Press the Enter button.

• Drag down the Fill Handle icon to copy the formula for the other cells.

• We get random values for each name.

• Use the following formula in Cell E5:
`=INDEX(\$B\$5:\$B\$12,RANK(C5,\$C\$5:\$C\$12))`
• Hit Enter.

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

The INDEX function will extract the value according to the number.

• Use the Fill Handle tool to copy the formula and get the other two random values.

• We got three random values.

## Method 4 – Randomly Select Multiple Unique Values Using INDEX, RAND, RANK, and MATCH Functions

Steps:

• Use Method 3 to fill column C.
• Insert a new helper columns.
• Use the following formula in Cell D5:
`=RANK(C5,\$C\$5:\$C\$12)`
• Hit Enter.

• Use the Fill Handle tool to copy the formula.

• This gives each person a random rank.

• Insert the following 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)

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

• Use the Fill Handle tool to get the other two random values.

• We got three different random names. Each time you will recalculate, you will get new names.

## Method 5 – Using XLOOKUP to Randomly Select Multiple Values Without Duplicates in Excel

Steps:

• Use Method 3 and 4 to fill in two helper columns C and D.
• Use the formula given below in Cell G5:
`=XLOOKUP(F5:F7,D5:D12,B5:B12)`
• Press the Enter button to get the array.

• Here are our random names as an array output.

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF