Excel’s utility is not limited to performing calculations or recording data. For Instance, you can even use Excel to draw out random names from the lists to pick a lucky winner. So Awesome, right?
With the online tools, you’d have to input hundreds and thousands of data to generate any one random value. But in Excel, you just have to use a single formula for your existing data.
Unlike RAND() or RANDBETWEEN() functions, there is no default function dedicated to selecting random values. However, we will nest these functions with others and create a simple formula to do so. So, if you wish to extract random values from your data, keep reading!
Case 1: Select a Random Value
To select random values, we will nest INDEX, RANDBETWEEN, and ROWS functions. This formula is best if you need to generate only one random value. As we are including the RANDBETWEEN function, you’ll have duplicates when you extract more than one value.
Firstly, let’s see the syntax and description of each function from the given table.
| Function | Syntax | Description |
| INDEX | INDEX(array, row_num, [column_name]) | Extracts the exact value from the specified column, row, and array. For Instance, you have a list of first names in Columns B2 to B5. If you enter =INDEX(B2:B5,2,1), it’ll look for the value in the first column and second row from B2 through B5. Then, return the name. |
| RANDBETWEEN | RANDBETWEEN(bottom, top) | Returns the random numbers between two values. The bottom should be lower and the top must be a larger number. For Example, =RANDBETWEEN(1,15) returns a random number between 1 to 15. |
| ROWS | ROWS(array) | Returns the number of rows from the specified cell reference. For Example, =ROWS(A2:A5) returns 4. |
Let’s say, I need to draw out a random name from cell ranges B2:B15. For this, I’ll nest all of these three functions and enter the formula as
=INDEX(B2:B15, RANDBETWEEN(1, ROWS(B2:B15)),1)
- The ROWS(B2:B15) formula first returns the number of rows from B2 through B15 which is 14.
- Then, the RANDBETWEEN(1, ROWS(B2:B15)),1) will extract a random integer between 1 to 14.
- Finally, the INDEX(B2:B15, RANDBETWEEN(1, ROWS(B2:B15)),1) returns the random name from 1 to 14 Rows.
Case 2: Select Random Values without Duplicates
The above formula is effective to select only one random value from the data. But, if you wish to generate multiple random values without any duplicates, we have a different formula. Here, we will use the INDEX, SORTBY, RANDARRAY, COUNTA, and SEQUENCE functions.
Again, before we jump to the formula, we will take a look at what each function means. For the INDEX function, you can see the above table.
| Function | Syntax | Description |
| SORTBY | SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2]…) | Returns the sorted array based on column and sort order. For Example, I have Ivan, George, and James names from cell A1:A3 sequentially. If I enter =SORTBY(A1:A3, {3;2;1}), it’ll return the names in James, George, Ivan order. |
| RANDARRAY | RANDARRAY([rows], [columns], [min], [max], [whole_number] | Returns an array of random values. Here, =RANDARRAY() returns random numbers like 0.836415. You can specify the number of rows and columns, minimum, maximum, or whole numbers to return the value. However, these all arguments are optional. |
| COUNTA | COUNTA(value1, [value2],..) | Counts and returns the number of non-empty cells. For Instance, =COUNTA(A1:A3) returns 3 if there are no empty cells from cells A1 through A3. |
| SEQUENCE | SEQUENCE(rows, [columns], [start], [step]) | Returns the array of rows and columns in a sequence. For Example, =SEQUENCE(2,2) will return the 1234 number in 2 columns and 2 rows. |
Suppose, I need to draw out 5 random names from the Column B lists with no duplicates. For this, we nested all the functions and entered the given formula
=INDEX(SORTBY(B2:B15, RANDARRAY(COUNTA(B2:B15))), SEQUENCE(5))
Let’s see how this formula returned only unique random values from Column B.
- COUNTA(B2:B15): It returns the total number of non-empty cells from the B2:B5 range. Since there aren’t any blank cells, it returned 14.
- RANDARRAY(COUNTA(B2:B15))): RANDARRAY returns a random value from 14 rows.
- SORTBY(B2:B15, RANDARRAY(COUNTA(B2:B15))): SORTBY function arranges the value of B2:B15 returned by RANDARRAY in irregular order.
- INDEX(SORTBY(B2:B15, RANDARRAY(COUNTA(B2:B15))), SEQUENCE(5)): Finally, the INDEX function returns 5 rows of random names.
Alternatively, you could use the same formula to extract the Random numbers from the data. Here, our SSN number data is in Column A. So, the formula would be
=INDEX(SORTBY(A2:A15, RANDARRAY(COUNTA(A2:A15))), SEQUENCE(5))