Dataset Overview
Let’s assume we have the following dataset. Now we want to select a random winner from the list.
Method 1 – Select a Random Winner Using Formulas
- Retrieve a Random Unique ID
- In cell G5, enter the following formula to retrieve a random unique ID from your dataset:
=INDEX(B5:E14,RANDBETWEEN(1,ROWS(B5:E14)),1)
How Does the Formula Work?
➤ ROWS(B5:E14)
The ROWS function returns the number of rows in a reference or array.
Output: 10
➤ RANDBETWEEN(1,10)
The RANDBETWEEN function returns a random number between two specified numbers.
Output: 7
➤ INDEX(B5:E14,7,1)
The INDEX function returns the value or reference of the cell at the intersection of a particular row and column, in a given range.
Output: “RD22A007”
- Retrieve Other Information Associated with the Winner:
- To retrieve additional information (e.g., email) associated with the winner, use the VLOOKUP formula.
- In cell G8, enter:
=VLOOKUP($G$5,$B$5:$E$14,4,FALSE)Replace 4Â with the appropriate column index for the desired information.
- Select a New Winner
- Take note of the winner’s details and notify them.
- To choose a new winner, press F9 to recalculate the formulas.
Method 2 – Use the RAND Function
- Generate Random Decimal Numbers
- In cell F5, enter the formula:
=RAND()
-
- Drag the fill handle down to copy the formula to other cells.
- This creates a list of random decimal numbers.
- Select a Winner
- Choose any random number from the list or select the top one.
- Press ALT+H+S+S to sort the numbers.
- The associated ID and information will be at the top of your dataset.
Things to Remember
- Note down the winner immediately to notify them.
- As these formulas use volatile functions, results will change whenever the worksheet is modified.
- You can refresh the sheet using the keyboard shortcut or add a refresh button using VBA.
Download Practice Workbook
You can download the practice workbook from here:
<< Go Back to Random Selection in Excel | Randomize in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Very cool. If we want to have 3 winners — say 1st prize, 2nd prize, 3rd prize — how could the chosen winner be removed or excluded so he isn’t selected again? Thanks!
Dear PA,
Thanks for your appreciation and valuable question. If you want to have 3 winners without repetition, you can follow the following procedure.
Create a column with random values using the following formula.
=RAND()
Then, apply the formula mentioned below in another column to rank the random values.
=RANK(D5,D5:D14)
Now, Insert the following formula where you want to have the winners’ names.
=XLOOKUP(C17:C19,E5:E14,C5:C14)
and press Enter to have multiple winners.
I hope this is what you are looking for.
Best regards,
Naimul Hasan Arif