Random name selector

In this article we will learn how to pick the name randomly from a list, so we will use the INDEX formula along with the ROUND, RAND and COUNTA formulae in Microsoft Excel 2010. We use these functions for Quiz, Games and Lottery etc, where there is no criteria defined for the selection.

Let’s take an example to understand how to select random names from a list in excel:-

Example: We need random number selection i.e. one of the values from the list in column A which contains a list of Serial Nos.

Follow the below mentioned steps:-

Select the cell B2, and write the formula
=INDEX($A$2:$A$7,ROUND(RAND()*COUNTA($A$2:$A$7),0))
Press Enter on the keyboard
The function will return the value 10.

The value will be returned by the formula and will change with each recalculation.
To understand how it works, select the cell B2 press the key F2 on the keyboard and press enter again.
The function will return the changed value from the list provided in column A.

This is all about that how you can select the random number from a list now we will do a simple exercise to select random text from a list.

We have data in range A1:B7, in which column A contains serial numbers and column B contains the student name. This data belongs to a school, where the application was submitted foradmission. The school is having only 3 seats, while the applicatns are 6. The school’s management wants to select the students randomly without being partial.

To randomly select the student name from the list, follow the below given steps:-

Select the cell D2, and write the formula
=INDEX($A$2:$A$7,ROUND(RAND()*COUNTA($A$2:$A$7),0))
Press Enter on the keyboard.
The function will randomly pick any name from the list.
The value will be returned by the formula and will change with each recalculation.
To randomly select 2 more students from the list copy the formula and paste it into D3 and D4.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Alyson Wright
December 9, 2014 at 2:10 am

The formula works for me. However, I am working from a list of 90 names and I want to choose 45 names. The problem is that there are repeats in 45 names. How do I make sure that once a name is selected, it cannot be selected again?

Reply ↓
- Trevor Gross
  September 5, 2019 at 1:59 pm
  
  Alyson Wright did you figure this out?
  
  Reply ↓
- Exceltip
  September 14, 2019 at 4:47 am
  
  You can do it by having a helper column adjacent to the name column. If you have names in A2:A91 then write =Rand() in B2:B91.
  Now in C2 write this formula get unique random names. =INDEX($A$2:$A$91,RANK.EQ(B2,$B$2:$B$91)). Copy down to C46 to get 45 unique random names.
  
  Reply ↓
Nisha Dhawan
September 9, 2014 at 8:58 am

Hi Hank,

Use this formula =RANDBETWEEN(A1,25)

Reply ↓
Hank
September 8, 2014 at 3:50 pm

Hi, I have a row of specific numbers and I want to generate a random number from one of the numbers in that row. Please help. I could do it in Quattro Pro but cannot in Excel.

Reply ↓
Roberto
August 5, 2014 at 9:23 pm

Amazing formula but gives me #VALUE quite often. Specially in short list.

Reply ↓
- Marty Braime
  December 5, 2014 at 6:55 pm
  
  I experienced the same. I changed to formula to this and haven't seen #VALUE.
  
  =INDEX($A$2:$A$7,RANDBETWEEN(1,COUNTA($A$2:$A$7)))
  
  Reply ↓