What it does – Generates a random name from a list of names in Google Sheets
Syntax:=INDEX(
reference
,
RANDBETWEEN(1,
COUNTA(range))
)
reference – range of cells with values
RANDBETWEEN – It generates a random number between 1 to n
COUNTA – Counts number of cells (containing value) in the selected range
Sample usage:=INDEX(A4:A, RANDBETWEEN(1, COUNTA(A4:A)))
// This will display a random name from a list of names in a column A (A4:A) in Google Sheets
Sample Google Sheets template with formula here.
Google Sheets offer a variety of tools that you can use to your advantage. Do you know how to pick a random name from a list in Google Sheets? It might seem to be tricky but the procedure is quite simple and easy to implement.
There are instances where one has to pick up something randomly like a lucky draw or randomly assigning tasks to someone. In those cases knowing how to pick a random name from a list in Google Sheets might come in handy.
In this tutorial, we will learn the step-by-step process of how to pick a random name from a list in Google Sheets.
Prerequisites
Before beginning the tutorial, we need to learn about a few functions that will be used in our formula. Understanding these functions will give you a better grasp of the tutorial.
RANDBETWEEN function in Google Sheets
The RANDBETWEEN function in Google Sheets is a random number generating function. The syntax of the RANDBETWEEN function is as follows:
=RANDBETWEEN(low, high)
A brief description of the syntax is as follows:
- Low: It stands for the lower end of the range
- High: It stands for the higher end of the range
- The random number generated lies between low and high
COUNTA function in Google Sheets
The COUNTA function in Google Sheets is used to count the number of cells in the selected range containing some value. The syntax of the COUNTA function is as follows:
=COUNTA(x:y)
A brief description of the syntax is as follows:
- X: It is the starting range of the dataset
- Y: It is the ending range of the dataset
INDEX function in Google Sheets
The INDEX function in Google Sheets is used to return the content of a cell by specified by row and column offset. The syntax of the INDEX function is as follows:
=INDEX(reference, row, column)
A brief description of the syntax is as follows:
- Reference: The range of cells where the values are located
- Row: The index of the row to be returned from within the reference range of cells
- Column: The index of the column to be returned from within the reference range of cells
Pick a random name from a list in Google Sheets
We are now equipped with the knowledge of various functions which we will use. In the following example, we have a column full of names of the people who participated in a lucky draw.

Our objective is to implement a function to pick a random name from a list in Google Sheets. The steps are as follows:
- Select the empty cell under the Winner header

- Begin your function with the ‘=’ sign. Type in the ‘RANDBETWEEN’. This function is used to generate a random value. The Google Sheets will prompt this function, press the Tab/Enter key to autocomplete. The tooltip guide will appear along with the details.

- Type in ‘1’ as the first argument of the RANDBETWEEN function. It signifies the lower bound.

- Use the comma separator and then type in COUNTA function and specify the range of data. In this case, it will be A4:A and then complete the function. The function should look like this:
=RANDBETWEEN(1, COUNTA(A4:A)

- Press the Enter key and you will see an integer value. This value is the randomly generated index of the list of names.

- In order to display the name we will use INDEX function. The INDEX function must wrap the whole random function. The formula should look like this:
=INDEX(RANDBETWEEN(1, COUNTA(A4:A))

- We already have second argument of the INDEX function i.e the row number. We need to specify the range of data as the first argument. In this case it will be A4:A. The final formula should look like this:
=INDEX(A4:A, RANDBETWEEN(1, COUNTA(A4:A)))

- Press the enter key and you will see the name of the random person from the list

We have successfully learned how to pick a random name from a list in Google Sheets. The formula used here is quite simple and easy to use.
Conclusion
We have successfully learned how to pick a random name from a list in Google Sheets. We have gone through a step-by-step process along with a detailed explanation of the functions involved in the complete process. You can now easily pick a random name from a list in Google Sheets.
See Also
You have successfully learned how to pick a random name from a list in Google Sheets. Are you interested in learning more about how much you can succeed with Google Sheets? With so many powerful features of Google Sheets, you save time and effort.
We have several tutorials that cover tricks and tips in Google Sheets. You can discover them here.
Here are some articles you might be interested in:
https://blog.tryamigo.com/how-to-create-a-countdown-timer-in-google-sheets/
https://blog.tryamigo.com/sort-query-using-order-by-in-google-sheets/
https://blog.tryamigo.com/introduction-to-date-function-in-google-sheets/