Home > Formulas > Math Trig > Random Names

Excel Choose Random Names

These 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.

random list of names created with formulas

Choose Random Names from List

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:

  1. Chooser 365 - For Excel 365
    • Spill functions in cells D3 and E3 list numbers and names
    • random list of names created with formulas
  2. Chooser ALL - For all Excel versions
    • INDEX/MATCH functions, limit of 10 names
    • random list of names created with worksheet formulas

Video: Choose Random Names - Excel 365

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.

1) Name List in Excel Table

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()

name list for random selection

Named Ranges in Table

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

2a) Name Chooser 365

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

formula for number sequence

Enter a Number

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

enter a number within the limits

Randomly-Chosen Names List

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.

Number List

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.

formula for number sequence

Random Name List

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.

random list of names created with formulas

Save the Names

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.

2b) Name Chooser ALL

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

random list of names created with worksheet formulas

Enter a Number

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

enter a number within the limits

Randomly-Chosen Names List

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.

Number List

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.

formula for number sequence

Random Name List

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)))

How the Formula Works

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.

random list of names created with worksheet formulas

3) Save the Names

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.

Copy and Paste as Values

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)

copy names and paste as values

Get the Sample File

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.

Related Links

RAND and RANDBETWEEN

SUM Function

VLOOKUP Function

INDEX function and MATCH Function

Count Functions

INDIRECT Function

 

 

 

Last updated: March 20, 2024 9:28 PM