# How to Select Names Randomly in Excel (5 Useful Methods)

We have made a dataset as a List of Players.

### Method 1 – Using the RAND Function to Randomly Select Names in Excel

• Use the following formula in the D5 cell.
`=RAND()`

• Press Enter and use the Fill Handle by dragging the cursor while holding the bottom-right corner of the D5.

• We’ll get the outputs in the Random Column.

• Use the following formula using the INDEX and RANK functions in the E5 cell.
`=INDEX(\$C\$5:\$C\$14,RANK(D5,\$D5:\$D\$14))`

Formula Explanation

• RANK(D5,\$D5:\$D\$14)) → returns the rank of D5 e.0.640365959 among the values of cells D5:D14.
• \$C\$5:\$C\$14,RANK(D5,\$D5:\$D\$14)) → returns the names in cells C5:C14 and then gives the rank of the name in D5 cell i.e. gives the rank of Jane.
• INDEX(\$C\$5:\$C\$14,RANK(D5,\$D5:\$D\$14)) → returns the intercept name which happens from the interception of the output value of \$C\$5:\$C\$14,RANK(D5,\$D5:\$D\$14)) and the rows from C5:C14.

• Press Enter and use the Fill Handle to get other names randomly.

### Method 2 – Utilizing INDEX, RANDBETWEEN, and COUNTA Functions to Randomly Select Names in Excel

• Use the following formula in the D5 cell.

`=INDEX(\$C\$5:\$C\$14,RANDBETWEEN(1,COUNTA(\$C\$5:\$C\$14)),1)`

Formula Explanation

• COUNTA(\$C\$5:\$C\$14) → returns the number of counts from cell C5 to C14.
• Output10
• RANDBETWEEN(1,COUNTA(\$C\$5:\$C\$14)) → returns any random value between 1 and
• INDEX(\$C\$5:\$C\$14,RANDBETWEEN(1,COUNTA(\$C\$5:\$C\$14)),1) → returns the intercept name which happens from the interception of the output value of RANDBETWEEN(1,COUNTA(\$C\$5:\$C\$14)) and the rows from C5:C14.

• Hit Enter.

• Use the Fill Handle to get multiple outputs of random selected names.

### Method 3 – Using INDEX, RANDBETWEEN, and ROWS Functions

• Use this formula in the D5 cell.
`=INDEX(\$C\$5:\$C\$14,RANDBETWEEN(1,ROWS(\$C\$5:\$C\$14)))`

Formula Explanation

• ROWS(\$C\$5:\$C\$14)→ Returns number of rows between cells C5 to C14.
• RANDBETWEEN(1,ROWS(\$C\$5:\$C\$14))→ Returns a random number between 1 and number of rows.
• INDEX(\$C\$5:\$C\$14,RANDBETWEEN(1,ROWS(\$C\$5:\$C\$14)))→ The number returned by RANDBETWEEN is fed to the row_num argument of the INDEX function, so it picks the value from that row. In the column_num argument, we supply 1 because we want to extract a value from the first column.

• Press Enter to get the random name as output.

### Method 4 – Applying CHOOSE and RANDBETWEEN Functions to Randomly Select Names in Excel

• Insert this formula in the D5 cell.
`=CHOOSE(RANDBETWEEN(1,6), "Jason", "Milan", "Agar", "Austen", "Jane", "Peter")`

The parameter bottom of the RANDBETWEEN function is 1 and the top is 6. The result of this function is a random number between 1 and 6 which is the index_num parameter of the CHOOSE function. The values of the function are “Jason”, “Miller”, “Agar”, “Austen”, “Jane”, “Peter”.

• Press Enter to find the output as a random name.

### Method 5 – Using INDEX, RANK.EQ, and COUNTIF Functions

• Fill up the Random Column by using the RAND function.
• Insert the following formula in the E5 cell.
`=INDEX(\$C\$5:\$C\$14,RANK.EQ(D5,\$D\$5:\$D\$14)+COUNTIF(\$D\$5:D5,D5)-1,1)`

• Press Enter and use the Fill Handle to get all the outputs as random names.

## Things to Remember

• If we use the combination of INDEX, RANDBETWEEN and COUNTA functions, we can get random names with duplicates i.e. one name can repeat several times.
• Using the RAND function gives the random names without duplication.

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

Get FREE Advanced Excel Exercises with Solutions!
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF