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.