Random Text from a list of values

The random text function allows for a random text value to be returned from a pre-defined list of inputs.  An example of where this is useful is for selecting a winner from a completion.

random text

In order to select a winning entrant we need to use the following nested function


Deconstruction of the formula


The Rows() function counts the number of rows in an array, in the above example we use the following formula: –


This returns the value 5 as there are five rows of data in the array.


We want to select a value at random from the list.  As we can not perform this function on text we use a combination of the ROWS() and RANDBETWEEN() to pick an entry.  Below is the make up of the RANDBETWEEN() function.

RANDBETWEEN(Lower number, higher number)

For our purposes we want a random number between 1 and the number of entrants.  Making use of the ROWS() function we can specify that the higher number be the number of entrants.


This returns a numerical value in this case between 1 and 5, however this is not what we are after.


The final step is to add in the process is to return the text based on the numerical values calculated above.  We do this using the Index function.  As discussed here the index function returns the value of the cell based on co-ordinates supplied.


For example


If we would return the Marie Curie as it is the 2nd row down in this only column.

By combining all the functions we have discussed above we can randomly select a row to feed into the Index() function and return a value for that cell.

The final formula will look like this

random text 2