Skip to content

How to Pick a Random Value From a List

Here is the question:

I have a list of items, and I would like my spreadsheet to randomly select one of these items and display the result. How do I do this?

That is an excellent question! Let’s cover how this is done.

The Formula

=INDEX(listRange,RANDBETWEEN(1,COUNTA(listRange)))

We will be using the above formula to have our spreadsheet make a random selection for us.

This formula uses three main functions to work properly. Let’s take a closer look at each of these functions to understand each one, and then we will discuss how they all fit together.

The INDEX Function

The INDEX function has many different uses and will accept 2 to 4 arguments. For the purpose of this example, the INDEX function will only require 2 arguments.

We will refer to each argument as A and B.

=INDEX(A,B)

If you do not know what a function argument is, it is simply an input that is supplied to the function. The function then takes each argument (input), does something with those inputs, and returns a result.

In the case of the INDEX function, argument A represents the array (list of values) that will be examined by the function. Argument B represents the row number (position) of one of those array values.

=INDEX(list of values, position of one of those values)

With these two pieces of information, the INDEX function is able to return a result. This result is whichever value happens to be in the position specified by argument B.

Here is an example – consider the list of values below:

  1. John
  2. Molly
  3. Susan
  4. Bob

If argument B happens to be the number 3, then the INDEX function would return the value of “Susan” because this is the value in the number 3 position.

The RANDBETWEEN Function

This function is fairly straightforward to understand. This function requires two arguments (inputs) which we will refer to as C and D.

=RANDBETWEEN(C,D)

The RANDBETWEEN function will return a random value that is between the two arguments it is supplied. More specifically, argument C will represent the lower bound, and argument D will represent the upper bound.

It is also important to note that the arguments themselves are included in the list of possible values that can be returned by this function.

Here is an example:

=RANDBETWEEN(3,8)

This function will return one of the following values at random:

  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

The COUNTA Function

The COUNTA function must have at least one argument, but there is no limit to the amount of arguments it can be supplied. This function can have 1 argument or 100.

It is also important to note that a range (reference to a group of cells) can be supplied as an argument to this function. So you could have something like this:

=COUNTA(A1:B5)

The COUNTA function counts how many values exist within the list of arguments it is supplied with.

It is typically used to count how many cells within the supplied range (the range is the argument in this case) contain values. In other words, it counts the number of cells in a range that are not empty.

Here’s an example.

Let’s say that there is a value in Cell A1, Cell A3, and Cell A5, but there is no value in Cell A2 or Cell A4. Now let’s say that we write the following function:

=COUNTA(A1:A5)

This function will end up returning a value of 3 because 3 of the cells in the supplied range contain values and the other two cells are empty.

Putting It All Together

Now that you know how each function works, we will break down our original formula step by step so you can see how it ends up selecting a random value from a list.

The Original Formula

=INDEX(listRange,RANDBETWEEN(1,COUNTA(listRange)))

The first thing that calculates within our formula is the COUNTA function. This function takes a look at our listRange (the group of cells that contain our list) and counts how many values exist within these cells.

For our example, there are 12 values in our list.

After the first calculation step, our function now looks like this.

=INDEX(listRange,RANDBWTWEEN(1,12))

The next step is for the RANDBETWEEN function to return a random number between 1 and 12. For our example, let’s say that it happened to select the number 8.

=INDEX(listRange,8)

For the final step of our formula, the INDEX function will look at our list and tell us which value exists in the number 8 position. For our example, we’ll say that the value of “Meagan” exists in the number 8 position.

This being the case, our final randomly selected value will of course be “Meagan.”

Each time the spreadsheet re-calculates, the number of values in our list will be checked, a random number between 1 and the number of values will be chosen, and that random number will be the position that our INDEX function will use to find our value.

This is how you use your spreadsheet to select a value at random from a list.

If you would like to see this formula in action, below is a free spreadsheet you can download with our example built right in. Feel free to download it and take an extra look if you need to!

Pick a Random Value from a List

See It In Action

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top