You can easily create a random draw winner selection tool to pick a random name from a long list in Google Sheets.
Table of Contents
It is useful if you have a list of names in one column and want to draw between them.
Say you have a competition for your website, school, or workplace, and you need to pick a random winner from a long list.
So how do we do that?
I will show you a solution using the combination of three Google Sheets functions:
COUNTAto count the number of participants in the draw,
RANDBETWEENto pick a random number between 1 and the total number of participants,
INDEXfinally to match the randomly selected number with the corresponding name in the list.
Let’s dive right into real examples to see how to pick a random name from a long list in Google Sheets.
The Anatomy of the RANDBETWEEN and INDEX Functions
The Anatomy of the RANDBETWEEN Function
RANDBETWEEN function is a random number generating function. The syntax of the
RANDBETWEEN function is as follows:
Let’s see what each part of this means:
=the equal sign is just how we start any function in Google Sheets.
RANDBETWEENis our function. We will have to add the arguments into it to work.
lowmeans the low end (the smallest number) of the random range.
highis the high end (the biggest number) of the random range.
For example, we need a random number between 1 and the total number of names to pick a random winner.
We can write the exact number of participants if it is always the same. For instance, if there are always 12 names in our list, we can write this function in the following way:
However, it is highly possible that you either don’t know the exact number of names or it changes.
This is why the
COUNTA function is useful. It counts the number of cells in a selected range. Therefore, we use it with a column reference that contains all the names we want to include in the calculation.
If the list of the names is in column A starting from A2, then you can write the following formula:
This function returns a random number which we can use as the index of the selected winner.
Great, we know how to pick a random winner! But we need to see his name as well. So we need to identify the randomly picked number and show the corresponding name of the list.
The Anatomy of the INDEX Function
INDEX function is useful to return the content of a cell, specified by row and column offset.
The syntax of the
INDEX function is as follows:
=INDEX(reference, row, column)
Let’s dissect this thing and see what each part of this means:
=the equal sign is just how we start any function in Google Sheets.
INDEXis our function. We will have to add the arguments into it to work.
referencemeans the range of cells where the values are located.
rowis an optional argument. It means the number of offset row(s) from the range.
columnis also an optional argument, it means the number of offset column from the range.
We want to pick a random number from a long list. Hence, we will need to use the list of the names as
reference, and then the randomly selected number as
This way, we can match the randomly picked number with its corresponding name from the list.
A Real Example of Using RANDBETWEEN and INDEX Functions
Let’s see how we can use all this to pick a random name from a long list in Google Sheets.
As you can see in the image above, the combination of the three functions shows a randomly picked name from the list. The function is as follows:
=INDEX(A2:A, RANDBETWEEN(1, COUNTA(A2:A)))
Here’s what this example does:
- We have actively selected the cell (or box) under C2, where we want to put our randomly picked name. As you can see, we use the
INDEXfunction wrapping the
RANDBETWEENfunction wrapping the
- We need to give two arguments to the
INDEXfunction. First, we need to add the range of cells where the names are written. We select the cells from A2 until the end of column A as our first argument in the
- And then, we need the row variable, which is the randomly picked number. We write a
RANDBETWEENfunction here to select a random number between 1 and the total number of names.
RANDBETWEENfunction has two arguments,
high. The first argument (
low) means the smallest index in the list, which is 1.
- After that, the second argument (
high) is the largest number we would like to include in our random number picker, so it is the total number of names. We use the
COUNTAfunction to calculate how many names are written in column A, starting from cell A2, so the argument is A2:A.
- As you can see, the value ‘Geoffrey Richmond‘ was inserted into our selected C2 because this is the random name the function picked for us.
Try it out by yourself. You may make a copy of the spreadsheet using the link I have attached below and try it for yourself:
How to Pick a Random Name from a Long List in Google Sheets
- Simply click on any cell to make it the active cell. For this guide, I will be selecting C2, where I want to show my result.
- Firstly, start by writing the RANDBETWEEN function to pick a random number between 1 and the total number of names. Simply type the equal sign ‘=‘ to begin the function and then followed by the name of the function which is our ‘
randbetween‘ (or ‘
RANDBETWEEN‘, whichever works).
- Great! Now you should find that the auto-suggest box will pop-up with the names of the functions. The one we want is our
RANDBETWEENfunction. So make sure to click on the right one!
- Now what you need to do is select the
highvalues that you want to use the
RANDBETWEENfunction with. You need 1 as the
lowvalue, so you need to write it as the first argument of the function.
- After that, you need to calculate the total number of names, which will be the second argument. You need to use the
COUNTAfunction. After the first argument, type a comma and start typing the name of the function which is ‘
COUNTA’. Make sure to select the right one! Type a bracket after the
COUNTAfunction name (Google Sheets will auto-fill it most of the time).
- The argument we need in the
COUNTAfunction is the whole range of cells containing the names. Therefore, you need to include all the cells where it is possible to later have new additional names. So, in my example, I will be selecting the whole column A starting from cell A2. I need to write it as A2:A.
- Great! Close the brackets on both of the functions. So far, you created the random number picker that chooses a number between 1 and the total number of names.
- Now, you need to use the
INDEXfunction to show the name of the picked winner. The
INDEXfunction has to wrap the whole random number picker, so you need to type the name of the function at the beginning of the formula.
INDEXformula needs two arguments and one of them (the second one,
row) is the already written
RANDBETWEENformula. So you only need to add the first argument that is
reference. You need the range with the names here as
referenceargument, which is the range A2:A in my example. Let’s type this before the
- Finally, just close all the functions with the closing brackets ‘)‘ then hit your Enter key. You’ll find a randomly picked name in the cell C2.
That’s it, good job! You can now pick a random name from a long list together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂