In this article:

How to Randomly Pick a Name from A Long List in Google Sheets

Do you plan to run a raffle from a long list of names in your spreadsheet? Well, here is a quick solution you can implement in Google Sheets! Here are the steps:

Step 1: Open the workbook containing the long list of names to pick from. We will use this list of 41 fruits for this tutorial.

Google Sheet with list of fruit names in Column A
The range of the cells containing the list is A1:A41. This is important for our next step.

Step 2: On the cell where you want to write down the selected entry, add this formula:

=index(<range_of_list>,RANDBETWEEN(1, <number_of_entries>))</number_of_entries></range_of_list>

Where <range_of_list> is the range of the cells containing the list and the <number_of_entries> is the number of entries contained in the list. </number_of_entries></range_of_list>

The RANDBETWEEN function randomly selects an integer from 1 to a specified maximum number. The maximum number should match the number of items in the list.

If you are not sure of how many entries are in the list but they are all in a single column, you can write the range as follows:


So for our example, we can write our range as A1:A, setting the first entry as A1 and then followed by all the cells with entries in column A. To replace <number_of_entries>, we can use the COUNTA function, defining its range in the same manner:</number_of_entries>


Plugging them together, we can have

=index(<range_of_list>,RANDBETWEEN(1, COUNTA(<range_of_list>)))</range_of_list></range_of_list>

This equation is more flexible because it allows you to add or remove entries in the list!

Once you entered the formula, Google Sheets will then select an item from the list

List of fruit in Column A with a randomly selected entry in cell B1
Our fruit list, now with a selected winner in cell B1!

The RANDBETWEEN function runs whenever there is a change in the spreadsheet. You can type a random character on a blank cell and it will change the selected entry. Pretty neat, right?

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Adds an object to Hubspot
Create a Slack channel, and optionally add a topic or members
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
Makes any HTTP request and returns the response
Inserts given array below defined values in given worksheet
Sends an email using your Google account
Sends an email using your Microsoft Outlook account
Sends a Slack message
Sends an SMS message using your Twilio account
Updates cells with given values
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started