**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:

`COUNTA`

to count the number of participants in the draw,`RANDBETWEEN`

to pick a random number between 1 and the total number of participants,`INDEX`

finally 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

The `RANDBETWEEN`

function is a random number generating function. The syntax of the `RANDBETWEEN`

function is as follows:

=RANDBETWEEN(low, high)

Let’s see what each part of this means:

`=`

the equal sign is just how we start any function in Google Sheets.`RANDBETWEEN`

is our function. We will have to add the arguments into it to work.`low`

means the low end (the smallest number) of the random range.`high`

is 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:

=RANDBETWEEN(1, 12)

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:

=RANDBETWEEN(1, COUNTA(A2:A))

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

The `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.`INDEX`

is our function. We will have to add the arguments into it to work.`reference`

means the range of cells where the values are located.`row`

is an optional argument. It means the number of offset row(s) from the range.`column`

is 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 `row`

.

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`INDEX`

function wrapping the`RANDBETWEEN`

function wrapping the`COUNTA`

function. - We need to give two arguments to the
`INDEX`

function. 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`INDEX`

function. - And then, we need the row variable, which is the randomly picked number. We write a
`RANDBETWEEN`

function here to select a random number between 1 and the total number of names. - The
`RANDBETWEEN`

function has two arguments,`low`

and`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`COUNTA`

function 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
`RANDBETWEEN`

function. So make sure to click on the right one!

- Now what you need to do is select the
`low`

and`high`

values that you want to use the`RANDBETWEEN`

function with. You need**1**as the`low`

value, 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
`COUNTA`

function. 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`COUNTA`

function name (Google Sheets will auto-fill it most of the time).

- The argument we need in the
`COUNTA`

function 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
`INDEX`

function to show the name of the picked winner. The`INDEX`

function has to wrap the whole random number picker, so you need to type the name of the function at the beginning of the formula.

- The
`INDEX`

formula needs two arguments and one of them (the second one,`row`

) is the already written`RANDBETWEEN`

formula. So you only need to add the first argument that is`reference`

. You need the range with the names here as`reference`

argument, which is the range**A2:A**in my example. Let’s type this before the`RANDBETWEEN`

formula.

- 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. 🙂

## 5 comments

This worked like a charm! Thank you for explaining in such detail!

Very cool — thank you. Do you know who to turn that into a conditional formatting function, in order to highlight the name instead?

This was very helpful! I do have a question though – do you know how I can add a conditional format to the winners so that there are no repeats until all the names have been selected once?

Is there a way to ensure that no name is repeated in the random pick action? I am trying to schedule Social Media posts from a list of design names but I don’t want to repeat any item from the original list in the random list, I want each item randomly picked just once.

Thanks in advance 🙂

I have copied the formula and get an error.