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.

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.

pick random names from list1pick random names from list2

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

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

 

Related Posts

Excel XLOOKUP Function

Excel XLOOKUP Function was added into Excel as a beta feature in August 2019 and is now accessible exclusively in Microsoft 365. (as of July 2021). However, if you fall into this category and often deal with big sets of ...

Extract or Filter Top n values
filter on top n values

You might have been through this kind of situation where you need to filter out the top n values from the list having few values, and I am also pretty sure about it that you might have chosen to do ...

Extract matching values From Two Lists
extract matching values from two list1

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Extract all Partial Matches
extract all partial matches1

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it's not a big deal; because ...

Excel Filter Function (10 Examples)
excel filter function

This post will guide you how to extracts matched values using FILTER function in Microsoft Excel 365. And also will introduce that how to use FILTER function with same examples in Excel 365. Excel Filter Function The FILTER function "filters" ...

Extract Unique Items From A List
Extract Unique Items From A List In Excel1

This post will guide you how to extract unique itmes from a given list in Microsoft Excel. How to create a newly formula to get unique values from a range cells in Excel. The unique list of items is the ...

Extract all the matches with helper Column
how to extract all matches with helper column1

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match ...

Basic Rates Calculation by VLOOKUP Based on Weight Band

Microsoft Excel provides many functions that can execute logical test, search data, return current date and something else. They are very useful in daily work. And Excel VLOOKUP function is one of Excel most frequently used functions. It belongs to ...

Sidebar