How to Generate Random Number from List in Excel (4 Ways)

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:

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:

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:

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:

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:

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