Although there is no suitable or single function to extract a random number from a list in Excel, there are numerous ways to manually construct the formulas with the help of different functions to generate a random number from a given list of data. In this article, youâ€™ll get to learn all available methods to pull out random numbers from a range of cells with appropriate illustrations.

**1. Using INDEX and RANDBETWEEN Functions to Get a Random Number from a List in Excel**

**The INDEX function**Â returns a value or reference of the cell at the intersection of the particular row and column, in a given range. **The RANDBETWEEN function**Â returns a random number between two specified numbers. By using the **RANDBETWEEN** function as the second argument **(row number) **of the **INDEX** function, we can pull out a random value or number from a list.

In the following picture, **Column B** has ten integer values in sequential order. In **Cell D5**, weâ€™ll extract a random number from the list.

The required formula in the output **Cell D5** will be:

`=INDEX($B$5:$B$14, RANDBETWEEN(1, 10))`

After pressing **Enter**, the formula will return any of the numbers from the list in **Column B**.

Now if you want to get more random numbers, use the **Fill Handle** option to fill down from **Cell D5**. Youâ€™ll get more random numbers in **Column D** and some of them may appear as repeated values. But if you donâ€™t want to see the repeated values as random numbers then you can move to method 4 where the formula has been defined not to display any value more than once.

**2. Using INDEX, RANDBETWEEN, and ROWS Functions to Get a Random Number from a List**

In the first method, we defined the upper and lower limits of the **RANDBETWEEN** function manually. Now in this section, weâ€™ll define the upper limit of the **RANDBETWEEN** function with the help of **the ROWS function**. Here the ROWS function will count the number of rows present in the range of cells **B5:B14** and assign the counted value to the upper limit of the **RANDBETWEEN** function.

So, the required formula in **Cell D5** should be:

`=INDEX($B$5:$B$14,RANDBETWEEN(1,ROWS(B5:B14)))`

After pressing **Enter** and auto-filling a few cells under **D5**, youâ€™ll be shown the output as in the picture below.

In this formula, you can use **the COUNTA function**Â too instead of the **ROWS **function. Both of them will count the number of rows in the range of cells **B5:B14**. With **COUNTA** function in lieu of **ROWS** function, the formula would look like this:

`=INDEX($B$5:$B$14,RANDBETWEEN(1,COUNTA(B5:B14)))`

The result will be similar as shown in the picture in this section.

**3. Combining CHOOSE and RANDBETWEEN Functions to Extract Random Number from a List**

You can also combine **CHOOSE** and **RANDBETWEEN** functions to pull out random numbers from a list. **The CHOOSE function** returns the value based on the specified serial number of that value from a list. But a problem with the **CHOOSE** function is you cannot insert any range of cells or an array inside the function. Rather you have to input all data or the cell references manually inside the function which will take time.

In **Cell D5**, the required formula to extract random numbers from a list with the help of **CHOOSE** and **RANDBETWEN** functions will be:

`=CHOOSE(RANDBETWEEN(1,10),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10,$B$11,$B$12,$B$13,$B$14)`

After pressing **Enter **and filling down some other cells, youâ€™ll get the random numbers as shown in the following screenshot.

**4. Generating a Random Number from the List with INDEX and RANK.EQ Functions in Excel**

The previous three methods are responsible for showing repeated values sometimes while extracting random values from a list. But with the help of **INDEX** and **RANK.EQ** functions, now weâ€™ll be able to extract and display a number from the list only once.

But before getting down to the use of this combined formula, we have to prepare a helper column first in **Column C** with **the RAND function**. The **RAND** function will return the random decimal values between 0 and 1. The **RANK.EQ** function will rank these decimal values in ascending or descending order. Unless you specify the order, the function will rank the values in descending order.

Now based on these rankings of all the values starting from the first one in **Cell C5**, the **INDEX** function will extract the numbers from the range of cells **B5:B14**.

So, the required formula in the output **Cell E5** will be:

`=INDEX($B$5:$B$14,RANK.EQ($C5,$C$5:$C$14))`

Press **Enter**, autofill some of the other cells under** E5** and youâ€™ll get the random values from **Column B**. Youâ€™ll be able to fill down the cells up to **E14** and find the random values without any repetition as well as without seeing any error. But if you step down beyond **E14,** the cells starting from **E15** will show **#N/A** errors.

**Download Practice Workbook**

You can download the Excel workbook that weâ€™ve used to prepare this article.

**Concluding Words**

I hope, all these four methods mentioned above will now help you to apply them in your Excel spreadsheet while generating some random numbers from a list. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

**<< Go Back to Random Number in Excel |** **Randomize in Excel**Â **|Â Learn Excel**