HomeGoogle DocsSpreadsheetHow to Pick a Random Name in Google Sheets (Does Not Refresh)

How to Pick a Random Name in Google Sheets (Does Not Refresh)

Published on

It’s easy to pick a random name from a list in Google Sheets. But what about making it static or doesn’t refresh upon changes in Sheet?

Can we stop the picked random name from the list from updating every time we enter any value in any cell in Google Sheets?

Yes! We can achieve that by using LAMBDA, one of the revolutionary improvements we have seen in the recent past in Google Sheets.

My formula has one more feature.

A few users want to know how to avoid repeating the same random name when dragging the formula down. I’ve also implemented that feature.

Random Name Picker in Google Sheets: Formula that Doesn't Refresh

Basic Formula Logic: Random Number Offset

Assume we have the names of 13 students in cell range A2:A14 in the “Sheet 1” tab in Google Sheets.

Cell A1 is for the filed label “Name,” so we don’t consider that.

Selecting a name randomly from this range is simple. Here is that logic.

Here are the three main functions that we will use and their role.

  1. RANDBETWEEN: We will use it to generate a random integer between two values, inclusive. The number 1 will be the lowest, and 13 (count of names) will be the highest.
  2. COUNTA: We will use it to get the total number of names in the list to feed the highest value in the above function.
  3. INDEX: It will offset the random number returned by Randbetween in the list and get the content of that cell.

These three functions are enough to pick a random name from a list in Google Sheets, but the result won’t be static. It will update (refresh) when you make any changes to the Sheet.

Formula to Pick a Random Name from a Long List in Google Sheets

You can use the following formula in any cell other than range A2:A to get a random name from the given range (long list).

Formula_1:

=index(A2:A,randbetween(1,counta(A2:A)))

Range A2:A should contain your list of names. If A2:A is blank, the above formula will return an error.

Use the IFERROR function to return null in case of an error.

Formula_2:

=iferror(index(A2:A,randbetween(1,counta(A2:A))))

If you want to set a trigger, use the following formula instead. It will only execute if the text in cell B3 is “Yes.”

Formula_3:

=if($B$3="Yes",iferror(index(A2:A,randbetween(1,counta(A2:A)))),"")

But remember one thing! You have entered “Yes” in cell B3. The above code is still prone to refresh upon changes in other cells.

Don’t worry! We will learn below how to pick a random name from a list and make it static (doesn’t refresh) in Google Sheets.

We only want the random name generator to refresh when we change the value in cell B3.

Before learning that, let’s create a drop-down in cell B3.

A drop-down in cell B3 will help you to switch between “Yes” or “No” easily. So, first, create one.

Here is how to do it.

Trigger for Random Name Picker in Google Sheets
  1. Cell B3 must be your active cell.
  2. Go to Insert menu > Drop-down.
  3. Replace “Option 1” with “Yes” and “Option 2” with “No.”
  4. Select “Done.”

How Do I Pick a Random Name and Make It Static in Google Sheets?

When I first wrote the above formula back in 2018, if I remember correctly, there was no formula option to achieve this.

The issue was associated with the Randbetween volatile function, which refreshes upon changes in Sheet.

It causes changes in the random number used in the Index for offset.

Now, with the help of the Lambda function, we can make a random name generator that won’t refresh the result upon changes in Google Sheets.

Let’s modify our above formula.

Syntax: =lambda(x,if($B$3="Yes",x,""))(formula_2)

Please scroll up and see our formula_2. Copy it and replace the text formula_2 in the syntax above.

Here it is.

Formula_4:

=lambda(x,if($B$3="Yes",x,""))(iferror(index(A2:A,randbetween(1,counta(A2:A)))))

You can use this formula to pick a random name from a list and make it static in Google Sheets.

Get More Unique Names When Dragging Down the Formula

To implement this additional capability, we must stick with a data format.

I am implementing this feature based on the below data formatting/settings.

  1. A2:A is the cell range that contains the name list.
  2. The drop-down that contains “Yes” or “No” is in cell B3.
  3. Formula_4 is in cell C2.

Please scroll up and see the first image to get to know it.

The formula in cell C2 picks a random name from A2:A and makes it static.

We want additional unique random names when we copy-paste or drag the C2 formula down.

To achieve this, you must replace A2:A (twice in the C2 formula [formula_4]) with the following code.

filter($A$2:$A,not(regexmatch($A$2:$A,"^"&textjoin("$|^",true,$C$1:C1)&"$")))

But I don’t recommend it because Google Sheets will execute the same FILTER twice and may affect performance.

You can use the LET (a 2023 function) to avoid repeated calculations. So I prefer it.

Here is the final formula to pick a random name from a list, make it static, and get additional unique random names when dragging it down.

=lambda(x,if($B$3="Yes",x,""))(let(ftr,filter($A$2:$A,not(regexmatch($A$2:$A,"^"&textjoin("$|^",true,$C$1:C1)&"$"))),iferror(index(ftr,randbetween(1,counta(ftr))))))

Find the Sheet with the live formula below.

Example – Random Name

Resources

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

14 COMMENTS

  1. Thanks a lot! Your formula was very simpler compared to the one I found from another site. However, on that site, I learned I can simply press Ctrl+R to generate another name.

  2. Hi there,

    This is a great article! I’m having an issue where I have more than one of these in the same sheet and when I change one, the other formula resets. How do I make sure that changing one Yes to No doesn’t affect the other formula?

    Thanks!

    • Hi, Casey,

      Seems you want to pick multiple random names. So instead of keeping two lists and two formulas in the same sheet, make it as one list and use another formula.

      I have another tutorial. See the second link under the related post above.

      Best,

  3. Hi,

    Good day!

    I tried using the formula and I am sure that I have placed them in the right cells and columns. However, the picker seems to do not stop shuffling names. It runs like a loop.

  4. How can I control the trigger? I noticed the random value changes everytime I make any change in any other cell.
    How can I use the trigger only once so that the random value does not change on any other change?

    • Hi, Damon,

      I think you want to deliberately exclude one name appearing in the random list. In the formula replace the range A2: A (both in Index and Counta) with the filter formula as below.

      filter(A2:A,A2:A<>"the name to exclude")

      See if this works.

  5. Thank you for writing such an useful article. I am getting parse error whenever I try use function below in my google spreadsheet.

    =if(B3=”Yes”,iferror(index(A2:A,randbetween(1,counta(A2:A))),”No Name!”),””)

    It would be very helpful if you can help me in solving this problem.

    Thank you in advance.

    • Hi Ashish,

      If you copy my formula, you should retype all the double quotes in the formula. Because I’ve pasted the formulas in my post as blockquotes.

      Now I’ve modified it as a code. Either retype the double quotes or copy the formula again from my page.

      Hope this may solve the problem.

LEAVE A REPLY

Please enter your comment!
Please enter your name here