Hello all. We have already seen random name picker here – there we used the CHOOSE function. Let’s see how to use generate random name (text) using the INDEX function. Index function returns the value of the cell at an intersection of specified row and column. With Index as the base, we shall see how to use randarray and randbetween functions to generate random text.
First let’s look at Index & randarry combination. Randarray returns an array of random numbers. In the below example we have a list of names between A1:A17 and we need to generate the names from the list.
Formula used to pick random text / names: =INDEX(A2:A17,RANDARRAY(5,1,1,COUNTA(A2:A18),1))
Let’s break the formula – first the randarray within the index. Randarray generates random numbers in an array fashion when the number of rows, columns, min, max values are specified. In our example, we have given the rows as 5, columns as 1, min value as 1 and a counta function to generate max value (the reason we use counta is to ensure we take right numbers excluding any blank values in the list).
So we would return 5 rows, generate the number as a single column, atleast one name (min value). So randarray has given row_num attribute for the index function. Array value in the Index function is the list of names we have. The formula returns the row number based on the output of the randarray.
If you want the names to generate as array with multiple columns, just change the columns value in the randarray() function.
If you feel randarray() is complex, just replace it with randbetween. In our case we have 16 names so it would be randbetween(1,16) within Index.
My recommendation, when using such huge list of values, is to have a named range created. This will ensure if there are changes in the base list – like inserting new rows in the base data. Check here on how to create name ranges. In our example, I have create a name range for our list as “Name”. Here is how we replaced the range with “Name”
I hope you like this section. Please help us with your valuable feedback to help improving the content. Thanks for reading!