How to Randomly Select a Winner in Excel (2 Methods)

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:

Select Random Winner.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Pa May 1, 2023 at 5:49 AM

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!
- Reply
  Naimul Hasan Arif May 2, 2023 at 4:26 PM
  
  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