Pick a random value from a list in Excel

In this blog post, I will explain two Excel formulas that can be used to pick a random value from a List.

Following is a list of 6 contestants for a particular game.

For each round of the game, a player should be randomly selected from this list.

Let’s see how to pick a random contestant using Excel.

Random value using CHOOSE and RANDBETWEEN functions

RANDBETWEEN function in Excel can be used to generate a random number between the two numbers specified in it. The following formula will generate a random number between 1 and 6, every time when the Excel worksheet recalculates. Use the function key F9 to recalculate the worksheet.

=RANDBETWEEN(1,6)

CHOOSE function in Excel will return the value in the given position of a list. RANDBETWEEN function generates a random number for every recalculation and this number can be used inside the CHOOSE function to pick a random value.

=CHOOSE(RANDBETWEEN(1,6),B3,B4,B5,B6,B7,B8)

Random value using INDEX and RANDBETWEEN functions

INDEX function in Excel returns the value at a given position of a list or an array.

Applying the same logic of the the previous formula, we can pick a random player for each round by using the RANDBETWEEN function inside the INDEX function.

=INDEX(B3:B8,RANDBETWEEN(1,6))

Which formula to use?

For larger datasets, there in no doubt that you should for the formula using INDEX function.


RANDBETWEEN function in Excel

CHOOSE function in Excel

INDEX function in Excel