I want to Randomly Select N Numbers from a column. What’s the solution? In Google Sheets, you can use my formula to pick a set of random numbers or names from a column. The formula is the same for numbers, names and even dates.

You will find the below Random Selector formula very useful. With the help of my formula, you can do the following and more.

1. Pick a group of players randomly.
2. Randomly select students for different divisions.
3. Pick N number of winners in a lottery.
4. Exclude excess people randomly when you have limited Quota.

The Formula to Randomly Select N Numbers from a Column in Google Sheets

I can tell you how to use this formula to randomly select N numbers or names from a Column in Google Doc Sheets. You should make two changes in the formula to suits your purpose.

Master Formula:

=ArrayFormula(Array_Constrain(vlookup(Query({ROW(A1:A20),randbetween(row(A1:A20)^0,9^9)},"Select Col1 order by Col2 Asc"),{row(A1:A20),A1:A20},2,FALSE),10,1))

What are those changes?

1. The above formula is for the range A1:A20. For example, if you want to use this formula in a larger list of 400 rows, change the range in the formula A1:A20 to A1: A400.
2. This master formula randomly picks 10 numbers or names from column A. The number 10 at the last part of the formula indicates the number of random numbers or names to be returned. You can change that number.

For example, if your range is A1:A100 and if you set the above-said number 10 to 50, the formula would return 50 random names or numbers. If you set it to 100, it would just shuffle your values in the range A1:A100 and return all the hundred numbers or names.

Example: Pick 10 Random Names Out of 20 Names

The above formula in C1 has randomly selected 10 names from column A.

Formula Explanation

This section is useful if you want to know how the master formula above picks N’ number of Random items in Google Sheets.

Note: You can learn a lengthy formula by looking at the middle part of it. Any nested spreadsheet formula would normally develop from the middle. I am following this method to explain to you the above formula.

No doubt, without the function RANDARRAY, RANDBETWEEN or RAND we can’t pick random names or numbers in Google Sheets. In the above formula, I’ve used the RANDBETWEEN function.

RANDBETWEEN(LOW, HIGH)

Suppose your “low” value is 1 and “high” value is 20, this function would return a random number between 1 and 20, both the numbers inclusive.

For my formula purpose, I want to generate 20 random numbers. What’s the option? By default, there is no option. But you can tweak the RANDBETWEEN as below.

The breakup 1:

=ArrayFormula(randbetween(row(A1:A20)^0,9^9))

No doubt. Here we can apply a common practice that all the Google Sheets’ experts follow. That is feeding the “low” argument with the Row function =ArrayFormula(row(A1:A20)) can return the numbers (not random numbers) 1 to 20. Since I have chosen 0 as the power (^0) the formula returns the number 1, 20 times.

The “high” value is set to 9^9 that is equal to 387420489.

So the above example formula would return 20 unique random numbers between 1 to 9^9.

I can use any number like 100, 200, etc. instead of 9^9 in the RANDBETWEEN formula as the “high” argument. But a large number can ensure that the formula is not returning any duplicate values.

The output of the above RANDBETWEEN formula would be something like below (the numbers won’t be the same).

The breakup 2:

Here we are adding additional functions to the “breakup 1” formula.

=ArrayFormula(Query({ROW(A1:A20),randbetween(row(A1:A20)^0,9^9)},"Select Col1 order by Col2 Asc"))

Actually, I don’t want the above generated (please see the screenshot) random numbers. What I want is to generate a set of random numbers between 1 to 20. To do that I’ve virtually created a two column array.

See the above “breakup 2” formula. The first column is the serial numbers 1 to 20 returned by a ROW function and the second column is the above 20 numbers returned by the “breakup 1” formula.

These columns used as the range in Query and sorted the column 1 based on column 2 and limited the column output to 1.

The Query formula does this part. Now I have got a column with random numbers 1 to 20. I mean the number 1 to 20 in random order.

The breakup 3:

The below formula is the combination of the breakup 1 and 2 formulas above.

=ArrayFormula(vlookup(Query({ROW(A1:A20),randbetween(row(A1:A20)^0,9^9)},"Select Col1 order by Col2 Asc"),{row(A1:A20),A1:A20},2,FALSE))

This formula uses Vlookup to pick random names from Column A which is our goal.

VLOOKUP(SEARCH_KEY, RANGE, INDEX, [IS_SORTED])

Here the search_key is the above “breakup 2” formula which generates random numbers from 1 to 20.

The range should be our list of names in Column A1: A20. But we can’t use this since our search_key is the random numbers from 1 to 20.

Here again, I’ve generated a two column array. The first column is the row numbers 1 to 20 and the second column is the actual values (names) in column A1:A20.

The column index is 2 in the Vlookup. So the Vlookup function will use the search key (random numbers from 1 to 20) to search the first column in the range (numbers 1 to 20 in order) and return the names from column 2 if a match found.

This formula will return all the names in a random order. You can wrap this formula with the Array_Constrain to return the total random names as per your desired number, here 10. That is what I’ve done in my final formula.

That’s all. Whenever you want to pick some names or numbers randomly, use my formula.  Also, I hope you could learn the steps to generate the formula that randomly selects N numbers from a column in Google Sheets.

Related Reading:

1. Google Sheets: Macro-Based Random Name Picker.
2. How to Pick a Random Name from a Long List in Google Sheets.
3. How to Shuffle Rows in Google Sheets Without Plugin