Home > Formulas > Math Trig > Random Names ## Excel Choose Random NamesThese examples show how to use the Excel RAND function to select a specific number random names from an Excel worksheet list. In the screen shot below, number 3 is entered in cell B3, and there are 3 random names listed in column E. |

See how to choose a specific number of random names from a list in Excel, using the Excel RAND function, combined with other Excel functions.

There are two versions of the Excel Name Chooser:

**Chooser 365 - For Excel 365**- Spill functions in cells D3 and E3 list numbers and names
**Chooser ALL - For all Excel versions**- INDEX/MATCH functions, limit of 10 names

See how you can choose a specific number of random names in Excel 365, from a list of names on a worksheet.

The random name formula uses functions that are available in Excel 365 - RANDARRAY, HSTACK, SORT and TAKE.

**=TAKE(SORT(HSTACK(tblNamesB[Name], RANDARRAY(COUNTA(tblNamesB[Name]))),2) ,B3,1)**

Because the random numbers are created in the formula, you don't need a RAND column in the table with people's names.

In the sample file, there is a named Excel table, **tblNamesA** with 2 columns: **Name**, and **Rand**

**Name**- Type names in this column, in any order. You can sort the names later, or leave them as is**Rand**- Has a formula to create a random number:**=RAND()**

In both columns of the table, the data cells have been named:

**NameList**- Names column in tblNames**RandList**- Rand column in tblNames

These names are used in formulas on the Name Chooser sheets

**Tip**: You can see name details in the Name Manager, on Excel's Formulas tab

This name chooser can be **used in Excel 365**, or other versions that support Excel's new spill functions. For older versions of Excel, go to the Chooser ALL instructions.

There are 3 formula cells on the worksheet, shown in the screen shot below:

- B4: Number Limits - Shows minimum and maximum numbers that can be entered
- D3: Name Count - lists numbers for the chosen names
- E3: Names - list of chosen names

To see a list of random names, type a number in cell B3

- In cell B4, a formula shows the minimum and maximum numbers that can be entered.
**="1 to " & COUNTA(NameList)**

- The maximum is a count of the names in the NameList range.
- There is a data validation rule in cell B3, with the same minimum and maximum settings

After a number is entered in cell B3, the randomly-chosen names are listed in column E, with a number beside each name, in column D.

The SEQUENCE formula in cell D3 creates a list of consecutive numbers for the names, based on the number in cell B3.

**=IFERROR(SEQUENCE(B3),"--")**

If something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

This formula in cell E3 creates the random list of names.

**=IFERROR(FILTER(NameList,RandList>=LARGE(RandList,B3)),"--")**

Here's how the formula works:

- First, the
**LARGE**function finds the nth largest number in the RandList range, based on the number in cell B3.- In this example, that number is 3, so it will return the 3rd largest number in the RandList range.

- Next, the
**FILTER**function returns names from the NameList range, where the Rand number is greater than or equal to the nth largest Rand number. - Finally, if something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

**Important**: Every time the worksheet calculates, the list of randomly-chosen names **will update automatically**.

If you want to save the list of names that were selected, follow the steps in the Save the Names section, further down this page.

This name chooser can be used in **any version of Excel** - it does NOT use the new spill functions.

There are 2 formula ranges on the worksheet:

**D3:D12**: Name Count - lists numbers for the chosen names**E3:E12**: Names - list of chosen names

To see a list of random names, type a number in cell B3

- The worksheet is set up to show a maximum of 10 selected names
- There is a data validation rule in cell B3, for whole numbers between 1 and 10
- That rule is shown in cell B4, for information only

After a number is entered in cell B3, the randomly-chosen names are listed in column E, with a number beside each name, in column D.

This formula is entered in cell D3, and copied down to cell D12. These formulas create a list of numbers for the names, based on the number in cell B3.

**=IF(MAX(D$2:D2)=$B$3,"",SUM(D2,1))**

If any number in the preceding rows is equal to the number in cell B3, the remaining cells show an empty string ("").

If something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

This formula, entered in cell E3 and copied down to E12, creates the random list of names.

**=IF(D3="","",INDEX(NameList, MATCH(LARGE(RandList,D3), RandList,0)))**

Here's how the formula works:

- First, the
**LARGE**function finds the nth largest number in the RandList range, based on the number in column D, in each row.- In cell E3, that would be the largest Rand number, and in E4, it's the 2nd largest, and so on

- Next, the
**MATCH**function returns a row number, within the RandList range, where that Rand number is found. - Then, the
**INDEX**function returns the name from that row number within the NameList range - Finally, if something causes an error, such as no number entered in B3, the IFERROR function shows two hyphens as the result.

**Important**: Every time the worksheet calculates, the list of randomly-chosen names **will update automatically**. If you want to save the list of names that were selected, be sure to copy the current names,
and paste them as values, somewhere else.

This very short video shows the steps, using a different sample file. There are written steps below the video.

Follow these steps to save the current list of randomly-chosen names:

- Select the cells with numbers and names, and press Ctrl+C, to copy them
- Go to another sheet, and right-click a cell where you want to paste the list
- Then, in the popup menu, under Paste Options, click on Values (V)

**Random Name Selection**: Download the workbook with the Excel
Random Name Chooser examples. The formulas on the Chooser365 sheets work in Excel 365, or other versions with the new spill functions. For other versions of Excel, use the ChooserALL sheet. The zipped file is in xlsx format, with no macros.

INDEX function and MATCH Function

Last updated: March 20, 2024 9:28 PM