Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles RANDOM How to Use Excel to Set Up an Electronic Raffle

How to Use Excel to Set Up an Electronic Raffle

Excel allows us to set up an electronic raffle using the RAND, INDEX, MATCH and MAX functions. This step by step tutorial will assist all levels of Excel users to get the random winner in the electronic raffle.

Figure 1. The final result of the formula

Syntax of the RAND Formula

The generic formula for the RAND function is:

=RAND()

The function returns a random decimal number between 0 and 1 and has no parameters.

Syntax of the INDEX Formula

The generic formula for the INDEX function is:

=INDEX(array, row_num, column_num)

The parameters of the INDEX function are:

  • array – a range of cells where we want to get a data
  • row_num – a number of a row in the array for which we want to get a value
  • column_num – a column in the array which returns a value.

Syntax of the MATCH Formula

The generic formula for the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])

The parameters of the MATCH function are:

  • lookup_value – a value which we want to find in the lookup_array
  • lookup_array – the array where we want to find a value
  • [match_type] – a type of match. We put 0 which is an exact match.

Syntax of the MAX Formula

The generic formula for the MAX function is:

=MAX(number1, [number2], ...)

The parameters of the MAX function are:

  • number1, [number2] – the numbers from which we want to get the maximum values

Setting up Our Data for the Electronic Raffle

Our table consists of 2 columns: “Name” (column B) and “Value” (column C). In column “Value” will be placed random numbers from 0 to 1 that will help us in the calculation. The second table has an empty cell E3 where we will get the winner name of the electronic raffle.

Figure 2. Data for the electronic raffle

Set Up an Electronic Raffle with Excel Functions

We want to get the random name from column B and to place the result in the cell E3. In order to make the formula more clear, we will create a named range Name for cell range B3:B7 and a named range Value for the cell range C3:C7.

To create a named range we should follow the steps:

  • Select the cell range that should be named
  • Click on the name box in Excel
  • Write the name for the cell range and press enter

Figure 3. Creating a named range Name for column “Name”

Figure 4. Creating a named range Value for column “Value”

The formula in Column C looks like:

=RAND()

The RAND function gets the random value from 0 to 1. The randomized values are placed in column C and will help us to get the winner in the electronic raffle.

To apply the formula, we need to follow these steps:

  • Select cell C3 and click on it
  • Insert the formula: =RAND()
  • Press enter.
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 5. Using the RAND formula to get a random value between 0 and 1

Now we can place the formula in the cell E3 to get the winner of the electronic raffle:

=INDEX(Name,MATCH(MAX(Value),Value,0))

The parameter array in the INDEX function is a named range Name while the row_num is the formula MATCH(MAX(Value),Value,0). In the MATCH function, lookup_value is a formula MAX(Value) while the parameter lookup_array is the named range Value. A match_type is 0 because we want the exact match. Finally, the parameter number1 in the MAX function is the named range Value.

To apply the formula, we need to follow these steps:

  • Select cell E3 and click on it
  • Insert the formula: =INDEX(Name,MATCH(MAX(Value),Value,0))
  • Press enter.

Figure 6. Get the winner of the electronic raffle with RAND, INDEX, MATCH and MAX functions

With MAX function we get the maximum value from column “Value”. The MATCH function searches maximum value in the column “Value” and retrieves the position of that value in the named range Value. The INDEX function gets the name from column B based on the MATCH function result, the row number 3. As a result, the winner of the electronic raffle is Jay.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Solution examples
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
I need a formula to calculate the MAX of a range of 167 rows in column D, then find the MAX of the next 167 rows in column D and so on continuously through the entire sheet. Example MAX D2:D169, D170:D337, D338:D505 etc...
Solved by I. Q. in 60 mins
I need a formula that will compare multiple columns, and return the column header name of whichever column had the highest value. I am using (INDEX($F$1:$K$1,0,MATCH(MAX(F2:K2),F2:K2,0))) , but one row had all the same values and the formula returned the first column header as being the "winner". This formula cannot distinguish when there are ties.
Solved by M. C. in 51 mins
Hello, I have a big spreadsheet in which I need to know how many patients came in each month based on provider. I am using the following formula but I still get the N/A error. =INDEX('No Show Appts Data'!D:E, MATCH(1, 'No Show Appts Data'!D:D='No Shows Data'!M2)*('No Shows Data'!E:E='No Shows Data'!N1),0)) No Show Appts Data is the name of the sheet where Column D is Month Year of date in question and Column E is the provider. No Shows Data is the sheet where I am making all the formula calculation where Column M is Month and Year and Column N is the provider in question and therefore M2 is the month in question and N1 the provider in question. How do I fix this error? Thanks
Solved by F. H. in 40 mins
I need to find an INDEX function that will convert the Call Day (a number 1-7) to the actual weekday found in row 1 of the DayofWeek named range. I don't know if I'm supposed to use the MATCH function as well or not.
Solved by D. D. in 11 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc