# Randomly select three names from a list

I have a list of names, where each name is in cells `D2` through `O2`. I want to select three names randomly.

How can I do that?

Here is my best attempt so far:

``````=ArrayFormula( UNIQUE(QUERY( SORT( D2:O2 , RANDBETWEEN( SIGN( ROW( D2:O2 ) ) , 1E+293 ) , 1 ) , "select * limit 20" ) ))
``````

When I do this, I get an odd error:

Array result was not expanded because it would overwrite data in O110

where the formula was input to cell `O110`.

• I made this work by moving my range to a column. Not sure why that worked... May 15 '17 at 21:46

Here is a solution along the lines of what you attempted, but simpler. First, a version for the case when the names are in a column, such as A2:A20 (which I think is more typical):

``````=query({A2:A20, arrayformula(randbetween(0, 1e20+row(A2:A20)))}, "select Col1 order by Col2 limit 3", 0)
``````

Explanation:

• `arrayformula(randbetween(0, 1e20 + row(A2:A20)))` creates a matching array of random numbers. One has to use `randbetween` and not `rand`, because it's necessary to pass an array argument, and `rand` does not accept arguments. The number `1e20` is "something that's large enough": we want as much entropy as possible in the random numbers, which is 64 bit in the double-precision format used by Sheets. `1e20` covers that easily.
• `{A2:A20, ...}` create a two-column array
• `select Col1 order by Col2 limit 3` picks the three entries with smallest random numbers.

Data in a row: this requires a small adjustment in the construction of the array: it's made of two rows and then transposed for querying. If the data is in A2:O2, it would be

``````=query(transpose({A2:O2; arrayformula(randbetween(0, 1e20+column(A2:O2)))}), "select Col1 order by Col2 limit 3", 0)
``````