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.

**Table of Contents**hide

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**4 Suitable Methods to Generate Random Number from a List in Excel**

**1. Use of INDEX and RANDBETWEEN Functions to Get a Random Number from a List**

The **INDEX** function returns a value or reference of the cell at the intersection of the particular row and column, in a given range. **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.

**Read more: ****Random Number Generator between Range in Excel**

**2. Use of INDEX, RANDBETWEEN, and ROWS Functions to Get a Random Number from a List in Excel**

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)))`

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

**Read more:** **Random Number Generator in Excel with No Repeats**

**3. Use of 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. 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.

**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.