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.

Table of Contents

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