In this blog post, we look at how to pick a name at random from a list. There are many reasons you may need this kind of random selection from a list.
It may be a neat way of deciding who is next to do the washing up, or to make a cup of tea :). Or it is useful for randomly drawing teams for a knockout cup competition.
To perform this random selection, we are going to use two different functions together. They are INDEX and RANDBETWEEN.
Excel Formula to Pick a Name at Random from a List
The INDEX function is used to return a value from a given cell in a range, and the RANDBETWEEN function is used to calculate a random number between two given numbers.
When used together, we can get the INDEX function to return a value (a persons name) from a random cell within its range.
The following formula has been entered into cell C3 to return a name at random from the list in A2:A13.
=INDEX(A2:A13,RANDBETWEEN(1,12))
The INDEX function used the 12 cells from A2:A13 as its array. The RANDBETWEEN function is then told to generate a random number between 1 and 12 and INDEX returns the value from that cell within its range.
So for example, if RANDBETWEEN returns the number 4, then INDEX returns the name from cell A5.
Want to learn more awesome formulas like this? Check out our Excel Formulas Made Easy online course.
Turn off Auto Calculations
The RANDBETWEEN function is a volatile function. This means that it calculates every time Excel does. Not all functions work this way, and normally only calculate when necessary to do so.
Because of this you may want to switch from automatic calculations to manual, otherwise the formula will constantly generate a random name, and not just when you need one.
To do this, click the Formulas tab on the Ribbon, then click Calculation Options and then Manual.
Now, to run calculations on the worksheet you can press F9.
Thanks Alan,
I have learned a lot from your course in the past.