A client contacted me to say they wanted to draw a prize winner out of an Excel database.
The RANDBETWEEN function allows you to find a random whole number between two numbers you specify, so combining this with the INDEX function would allow us to do this.
If you had 2000 names in the list, the function = RANDBETWEEN(1,2000) will give a whole number between 1 and 2000. This can be the row with the prize winner on, so we than have to find the name of the person on that row using the INDEX function, which returns the value that is in the cell at the intersection of the row and column you specify.
The syntax of the INDEX function is =INDEX(array, row number,column number), where the array is the data area you are searching.
So if we are searching column A for a name, we could use
=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1) where the COUNTA($A:$A) counts how many names are actually in the column.
Now you have your prizewinner.
Find more details about Excel training Sydney, Australia