Exceljet

Quick, clean, and to the point

Random list of names

Excel formula: Random list of names
Generic formula 
=INDEX(names,RANDARRAY(n,1,1,COUNTA(names),TRUE))
Explanation 

To create a random list of names, you can use the INDEX function and the RANDARRAY function to select random names from an existing list. In the example shown, the formula in D5 is:

=INDEX(names,RANDARRAY(10,1,1,COUNTA(names),TRUE))

which returns 10 random values from the named range "names" (B5:B104). 

How this formula works

At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called "names" which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this:

=INDEX(names,5)

However, the trick in this case is that we don't want a single name at a known location, we want 10 random names at unknown locations between 1 and 100. This is an excellent use case for the RANDARRAY function, which can create a random set of integers in a given range. Working from the inside out, we use RANDARRAY to get 10 random numbers between 1 and 100 like this:

RANDARRAY(10,1,1,COUNTA(names)

The COUNTA function is used to get a dynamic count of names in the list, but we could replace COUNTA with a hardcoded 100 in this case with the same result:

=INDEX(names,RANDARRAY(10,1,1,100,TRUE))

In either case, RANDARRAY will return 10 numbers in an array that looks like this:

{64;74;13;74;96;65;5;73;84;85}

Note: these numbers are random only and do not map directly to the example shown.

This array is returned directly to the INDEX function as the row argument:

=INDEX(names, {64;74;13;74;96;65;5;73;84;85}

Because we are giving INDEX 10 row numbers, it will 10 results, each corresponding to a name at the given position. The 10 random names are returned in a spill range beginning in cell D5.

Note: RANDARRAY is a volatile function and will recalculate every time the worksheet is changed, causing values to be resorted. To stop values from sorting automatically, you can copy the formulas, then use Paste Special > Values to convert formulas to static values.

Prevent duplicates

One problem with the above formula (depending on your needs) is that RANDARRAY will sometimes generate duplicate numbers. In other words, there is no guarantee that RANDARRAY will return 10 unique numbers.

To ensure 10 different names from the list, you can adapt the formula to randomly sort the full list of names, then retrieve the first 10 names from the list. The formula in F5 uses this approach:

=INDEX(SORTBY(names,RANDARRAY(COUNTA(names))),SEQUENCE(10))

The approach here is the same as above - we are using INDEX to retrieve 10 values from the list of names. However, in this version of the formula, we are sorting the list of names randomly before handing giving the list to INDEX like this:

SORTBY(names,RANDARRAY(COUNTA(names)))

Here, the SORTBY function is used to sort the list of names randomly with an array values created by the RANDARRAY function, as explained in more detail here.

Finally, we need to retrieve 10 values. Because we already have names in a random order,  we can simply request the first 10 with an array created by the SEQUENCE function like this:

SEQUENCE builds an array of sequential numbers:

{1;2;3;4;5;6;7;8;9;10}

which is returned to the INDEX function as the row argument. INDEX then returns the first 10 names in a spill range like the original formula.

This example uses "Dynamic Arrays", a beta feature in Excel currently available through the Office Insiders program. Expected release in 2019.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.