which returns 10 random values from the named range names (B5:B104). 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: 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: In either case, RANDARRAY will return 10 numbers in an array that looks like this: 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: 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: 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: 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: 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. Note: for a more detailed explanation of random numbers without duplicates, see this example.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
