How To Pick A Random Name From A List In Google Sheets

Reading Time: 5 minutes

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. 

Example to learn how to pick a random name from a list in Google Sheets

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
Select an empty cell
  • 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 the formula as shown
  • Type in ‘1’ as the first argument of the RANDBETWEEN function. It signifies the lower bound. 
Type in the parameters as shown
  • 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)
This function generates a random index
  • Press the Enter key and you will see an integer value. This value is the randomly generated index of the list of names. 
Random index generated
  • 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))
Use the INDEX function to wrap the formula
  • 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)))
Fill in the parameters as shown
  • Press the enter key and you will see the name of the random person from the list
A random name generated

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/

Anubhav Maheshwari

←
→

Leave a Reply