When creating a lucky draw or mock data, you might end up needing to generate a random name in Excel. As for now, Excel does not have a designated function that picks random names from a specified source.
However, you can construct custom formulas using Excel’s library functions to create such look-ups!
We will combine Excel’s lookup, mathematical, and statistical functions to build a formula according to the size, and structure of our data.
Generate a Random Name from a Small List
| Functions Used | Type | Syntax |
| CHOOSE | LOOKUP & REFERENCE | =CHOOSE(index_num,value1,[value_2]...) |
| RANDBETWEEN | MATH & TRIG | =RANDBETWEEN(bottom,top) |
Say, you’re organizing a lucky draw between five of your colleagues. In that case, you don’t need to write their names down in a column to create a formula.
We’ll be creating a formula using the CHOOSE and RANDBETWEEN functions. As we want to generate a random number between five names, we’ll use RANDBETWEEN to pick a number from 1 to 5.
Then, we will nest the formula inside CHOOSE, where we’ll be assigning a number to each name.
In cell A1, here’s the formula we constructed:
=CHOOSE(RANDBETWEEN(1,5),"Derek","Richard","Stacy","Samara","Lisa") // returns value according to the index number.
When RANDBETWEEN returns 3, CHOOSE returns “Stacy.” You can use F9 to refresh your sheet and generate a new value.
Generate a Random Name from a Range
| Function Used | Type | Syntax |
| ROW | LOOKUP & REFERENCE | =ROW() |
| VLOOKUP | LOOKUP & REFERENCE | =VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])] |
| RANDBETWEEN | MATH & TRIG | =RANDBETWEEN(bottom,top) |
For this example, we will be working on a bigger data set. In column A, I have a total of 1000 names of individuals who participated in an online lucky draw.
We cannot use the CHOOSE function as it is not an array function; you will have to reference each cell separately in the value section.
As this is pretty tedious, let’s use a better lookup function like VLOOKUP. Before entering the formula, I inserted a new column on the left.
On A1, I entered the following formula and applied it to the entire range:
=ROW() //returns the row number of the cell.
Now, enter the following formula in cell D3:
=VLOOKUP(RANDBETWEEN(1,1000),A1:B1000,2) //returns the value adjacent to the number generated by RANDBETWEEN in range A1:B1000
When RANDBETWEEN returns 969, VLOOKUP returns “Erda Slowgrove” as a result.
Generate Random Names using First and Last Names
| Function Used | Type | Syntax |
| INDEX | LOOKUP & REFERENCE | =INDEX(array,row_num,[column_num) |
| RANDBETWEEN | MATH & TRIG | =RANDBETWEEN(bottom,top) |
| COUNTA | STATISTICAL | =COUNTA(value1,[value2],...) |
If you’re looking to generate random names for mockups, your first and last names could be arranged in different columns.
We will be nesting COUNTA inside RANDBETWEEN to first generate the row_num for the INDEX function.
COUNTA will return the number of texts in the range. We will be using this value as our top value in RANDBETWEEN.
We have 20 first and last names in columns A and B, respectively. We need to generate a formula that randomly generates a full name using one of these values.
In cell E2, here’s the formula we entered to get a random list of full names:
=INDEX($A$2:$A$21,RANDBETWEEN(1,COUNTA($A$2:$A$21)))&" "&INDEX($B$2:$B$21,RANDBETWEEN(1,COUNTA($B$2:$B$21))) //returns a random concatenated value of texts in range A1:A4 and B1:B4
Generate Random Names in Excel without Duplicates
| Function Used | Type | Syntax |
| INDEX | LOOKUP & REFERENCE | =INDEX(array,row_num,[column_num]) |
| SORTBY | LOOKUP & REFERENCE | =SORTBY(array,by_arry1,[sort_order1],..) |
| COUNTA | STATISTICAL | =COUNTA(value1,[value2],...) |
| SEQUENCE | MATH & TRIG | =SEQUENCE(rows, [columns], [start], [step]) |
| RANDARRAY | MATH & TRIG | =RANDARRAY([rows],[column],[min],[max],[integer]) |
If you’re creating a random group of people, you need to make sure that your formula doesn’t repeat names from the list.
To avoid redundancy, we’ll introduce the SORTBY, RANDARRAY, and SEQUENCE functions into our formula.
We have a list of 20 names in column A. We need to randomly select 5 individuals from the list who will be in charge of a special project. Here is how we create such a list:
=INDEX(SORTBY(A2:A21,RANDARRAY(COUNTA(A2:A21))),SEQUENCE(5)) // returns five unique names from range A2:A21