How to create a formula to get a random text value from a list of cells in a spreadsheet


In the table above, I wanna know who I should call to hang out with on Friday night. I want the computer to pick a random dude from the spreadsheet. The formula below shows how we are going to do this.

=INDEX(list of dudes, RANDBETWEEN(1, ROWS(list of dudes)),1)

The RANDBETWEEN() picks a number between 1 and the number of rows in the sheet.

The index() tasks that number and finds the row and returns the data in the first column (that’s what the 1 is doing at the very end.

Let’s get the values for the data range in there:

=INDEX($A$1:$A$8,RANDBETWEEN(1,ROWS($A$1:$A$8)),1

You could replace the $A$1:$A$8 with a named range if working in excel (I’m not)

And the answer (tonight) is Francisco.

Leave a Reply

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