Excel allows us to extract random data from the list using the **RAND, INDEX **and **RANK** functions. This step by step tutorial will assist all levels of Excel users to get the random names from the list and to create a random team of three members.

* Figure 1. Get the random team members from the name list*

**Syntax of the RAND Formula**

The generic formula for the RAND function is:

**=RAND()**

The function returns a random decimal number between 0 and 1 and has no parameters.

**Syntax of the INDEX Formula**

The generic formula for the INDEX function is:

**=INDEX(array, row_num, column_num)**

The parameters of the INDEX function are:

**array**– a range of cells where we want to get a data**row_num**– a number of a row in the array for which we want to get a value**column_num**– a column in the array which returns a value.

**Syntax of the RANK Formula**

The generic formula for the RANK function is:

**=RANK(number, ref, [order])**

The parameters of the RANK function are:

**number**– a number which we want to rank**ref**– a list of numbers for ranking**[order] – 1 for ascending or 0 for descending order. This parameter is optional, if it’s omitted, the default order is descending**

## Setting up Our Data to Extract Random Data from the Name List

Our table consists of 2 columns: “Name” (column B) and “Value” (column C). In column “Value” will be placed random numbers from 0 to 1 that will help us in the calculation. The second table has column “Team members” (column E) where we will extract the names from column B.

* Figure 2. Data for the example*

**Get Random Names from the Table with RAND, INDEX and RANK Functions**

We want to get the random names from column B and to place the results in the column E. In order to make the formula more clear, we will create a named range **Name** for cell range B3:B14 and a named range **Value** for the cell range C3:C14.

To create a named range we should follow the steps:

- Select the cell range that should be named
- Click on the name box in Excel
- Write the name for the cell range and press enter

* Figure 3. Creating a named range Name for column “Name”*

* Figure 4. Creating a named range Value for column “Value”*

The formula in Column C looks like:

**=RAND()**

The RAND function gets the random value from 0 to 1. The randomized values are placed in column C and will help us to get the random names in column E.

To apply the formula, we need to follow these steps:

- Select cell C3 and click on it
- Insert the formula:
`=RAND()`

- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

* Figure 5. Using the RAND formula to get a random value between 0 and 1*

Now we can place the formula in the cell E3 to get the random team member:

**=INDEX(Name,RANK(C3,Value))**

The parameter **array** in the INDEX function is a named range Name while the **row_num** is the formula RANK(C3,Value). In the RANK function, the **number** is a cell C3 while the parameter **ref** is the named range Value.

To apply the formula, we need to follow these steps:

- Select cell E3 and click on it
- Insert the formula:
`=INDEX(Name,RANK(C3,Value))`

- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

*Figure 6. Extract random data with RAND, INDEX and RANK functions*

With RANK function we get the rank of the cell C3 in column C. Since the values are unique in the “Value” column the result from the RANK function is a row number input for the INDEX function. The RANK function result is number 1 and the INDEX formula retrieves the first entry in the named range Name. Final formula output is Mike, a random name from column B.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment