# How to Pick a Random Name from a List in Excel

This post will guide you how to pick a random name from a list in Excel. How do I select random value from a list or a table with a formula in Excel 2013/2016.

## 1. Pick Random Name from a List

Assuming that you have a list of names in range B1:B6, and you want to pick random names from this list. How to do it. You can use a formula based on the INDEX function, the RANDOMBETWEEN function and the COUNTA function to achieve the result. You can use one of the following formulas:

``=INDEX(\$B\$2:\$B\$6,RANDBETWEEN(1,COUNTA(\$B\$2:\$B\$6)),1)``

Or

``=INDEX(\$B\$2:\$B\$6,RANDBETWEEN(1,ROWS(\$B\$2:\$B\$6)),1)``

Type this formula into a blank cell and press Enter key on your keyboard. And then drag the Fill Handle down to list the random names that you need. And then press F9 key, it will generate the different name list randomly.

Note: you should know that the name list will be changed when you refresh the current worksheet.

## 2. Pick a Random Name from a List Using VBA Macros

In this tutorial, we’ll learn how to pick multiple random names from a list in Excel. We’ll use a VBA macro to do this, allowing you to select a source range of names, a destination range for the random names, and specify how many random names you want to pick.

Step1: To get started with the VBA method, press Alt + F11 to open the VBA editor in Excel.

Step2: In the VBA editor’s menu, click “Insert” and select “Module” to insert a new module.

Step3: Copy and paste the provided VBA code into the module.

``````Sub PickMultipleRandomNames()
Dim SourceRange As Range
Dim DestinationRange As Range
Dim NumNamesToPick As Integer
Dim i As Integer

' Select the source range containing names
On Error Resume Next
Set SourceRange = Application.InputBox("Select the source range containing names:", Type:=8)
On Error GoTo 0

If SourceRange Is Nothing Then
MsgBox "No source range selected. Macro aborted."
Exit Sub
End If

' Check if the selected range is valid
If SourceRange.Rows.Count < 1 Or SourceRange.Columns.Count <> 1 Then
MsgBox "Please select a single-column range with at least one name. Macro aborted."
Exit Sub
End If

' Select the destination range for the random names
On Error Resume Next
Set DestinationRange = Application.InputBox("Select the destination range for the random names:", Type:=8)
On Error GoTo 0

If DestinationRange Is Nothing Then
MsgBox "No destination range selected. Macro aborted."
Exit Sub
End If

' Ask the user how many random names to pick
On Error Resume Next
NumNamesToPick = InputBox("Enter the number of random names to pick:")
On Error GoTo 0

If NumNamesToPick <= 0 Then
MsgBox "Please enter a valid number of random names. Macro aborted."
Exit Sub
End If

' Pick and place the random names
DestinationRange.ClearContents ' Clear any existing contents in the destination range

For i = 1 To NumNamesToPick
Dim RandomRow As Long
RandomRow = Application.WorksheetFunction.RandBetween(1, SourceRange.Rows.Count)

Dim RandomName As String
RandomName = SourceRange.Cells(RandomRow, 1).Value

DestinationRange.Cells(i, 1).Value = RandomName
Next i

' Inform the user
MsgBox NumNamesToPick & " random names placed in the destination range."
End Sub
``````

Step4: Save your workbook and close the VBA editor.

Step5: Press ALT + F8 to open the “Macro” dialog. Select the “PickMultipleRandomNames” macro and click “Run“.

Step6: A dialog box will appear, prompting you to select the source range containing names. Click and drag to select the range and press “OK.”

Step7: Another dialog box will appear, this time asking you to choose the destination range where the random names will be placed. Select a cell in your worksheet and press “OK.”

Step8: A prompt will appear, asking you to specify how many random names you want to pick. Enter the desired number and click “OK.”

Step9: The macro will pick the specified number of random names and place them in the destination range.

You’ve successfully learned how to pick multiple random names from a list in Excel using a VBA macro.

## 3. Video: Pick a Random Name from a List in Excel

This video tutorial where we’ll explore two methods for picking a random name from a list using both formula-based and VBA macro methods in Excel.

## 4. Related Functions

• Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel COUNTA function
The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
• Excel RANDBETWEEN Function
The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….