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.

 

img1

 

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

 

img2

 

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.

 

img3

 

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.

 

img4

 

image 48

 

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

 
 

Users are saying about us...

    • 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)))

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

  2. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube