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.
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.
- 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.
- COUNTA: We will use it to get the total number of names in the list to feed the highest value in the above function.
- 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.
- Cell B3 must be your active cell.
- Go to Insert menu > Drop-down.
- Replace “Option 1” with “Yes” and “Option 2” with “No.”
- 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.
- A2:A is the cell range that contains the name list.
- The drop-down that contains “Yes” or “No” is in cell B3.
- 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.
Resources
- Google Sheets: Macro-Based Random Name Picker.
- How to Randomly Select N Numbers from a Column in Google Sheets.
- How to Randomly Extract a Certain Percentage of the Rows in Google Sheets.
- Formula to Generate Unique Readable IDs in Google Sheets.
- How to Generate Odd or Even Random Numbers in Google Sheets.
- Pick Random Values Based on Conditions in Google Sheets.
- How to Generate a List of Passwords in Google Sheets.
Thanks! your article saved me a lot of time!
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.
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,
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.
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?
It’s not possible as RANDBETWEEN is a volatile function like
now()
. There may be scripts but I am not sure.The other option is trying the “Randomise range” that you can find under the Data Menu. Select the range and apply “Randomise range”
Updated: Now It’s possible to stop refreshing the generated random name.
Hey there, this worked perfectly. Just wondering, could i change the chances of a particular name coming up?
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.
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.
How do i prevent the the random name generator from using the same names more than once?
Hi,
Please see if this is working!
Randomly Select Unique N numbers