Want to get free access to our Excel functions course?

Fill in your e-mail address and telephone and we will give you 7 days free access to our course The Excel Functions Guide.


RANDARRAY creates a list of a specified number of random values.

In this lesson, we want to grab 15 random names from our list of employees, split into three columns with 5 names per column.

With RANDARRAY and INDEX, we can determine exactly how many names we want to appear in separate cells.

We’ll first create the RANDARRAY function, which will return the corresponding position number of the randomly selected names.

We enter the cell we want at the top of the list and write =RANDARRAY and left parenthesis.

The first and second arguments are the number of rows and columns respectively that we want, which for us will be 5 and 3.

The third argument is the minimum value we want, which for us will be 1.

The fourth argument will be the maximum number of positions that will exist, and for this we create a COUNTA function, with all the names as the argument.

This ensures that we do not get position numbers that are higher than the number of names in the list.

For the fifth and final argument, we’ll need to specify whether we want integers, which we do, so we enter TRUE.

We’ll close the function, hit enter, and now have three columns and five rows of numbers between 1 and the number of names available.

To assign the names in the list to the random numbers, we’ll insert an Index function, with the name list as the first argument, and the RANDARRAY function as the second argument.

When we close the function with right parenthesis and hit enter, we now have a list of random names.

Keep in mind that because the values are random, the cells will be updated every time anything else in the sheet is updated, so it may be handy to copy the cells and paste values to preserve the names as they are.