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.

https://youtu.be/D5mGcE4SdJc

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