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.
- 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.
Download the Practice Workbook
<< Go Back to Random Selection in Excel | Randomize in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
