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

0
396
Google Sheets Function Guide

Ever wondered how to pick a random name from a long list in Google Sheets? With the help of this tutorial anyone can make a random name picker with the help of Google Sheets. You can use this as your free online random name selection tool to select lucky draw winners for internal competitions at your workplace, home, club etc.

Why Google Sheets to Create Random Name Picker When There are Lots of Such Tools Available Online?

The reason is simple. This is a tutorial to create a random name selection tool using Google Spreadsheets. So it’s intended for those who like the amazing capabilities of Google Sheets.

Formulas Used to Select a Random Name from a Long List in Google Sheets

I have used a combination of few formulas to generate random name from a long list in Google Sheets.

I have separate tutorials for each formulas used here to pick random names. So I believe I can cut short this tutorial to a nice length. The below are the essential formulas used.

  • RANDBETWEEN – I used this formula to generate a random number.
  • COUNTA – RANBETWEEN function requires low and high values to generate a random number between that numbers. You can put 1 as low value. High value depends the total names in your name list. So COUNTA can return that number.
  • INDEX – Index function returns content of a cell based on a specified row and column offset. Row and column offsets are optional in this function. But for our purpose we used here only the row offset. So we can use here the RANDBETWEEN generated number as row offset.

Click the above links to learn the functions in details.

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 generate a random name in Google Sheets.

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

Range “A2:A” should contain your list of names.

You can modify the formula as below. Use the below formula to avoid the above random name picker formula returns error when there is no content in range “A2:A”

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

If you want to set a trigger, use the following formula instead.

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

This time, in Cell B3, create a drop down menu that only containing text “Yes” or “No”. You can use data validation for this purpose.

And finally here is our random name picker in Google Sheets. Just enter or copy paste from somewhere the names to be included in the selection process. Select “Yes” under “Proceed Y/N?”

generate random name in google sheets using formula

Conclusion:

Hope you could successfully create this random name picker in Google Sheets. If you have faced any issue, feel free to ask me for the copy of the file I’ve created. Please use the comment form to notify me. Thanks.

LEAVE A REPLY

Please enter your comment!
Please enter your name here