Create unique email address

Occasional Contributor

Hi all, 


I want to create unique email addresses for all the new employees in my firm. I will get the first name and last name from the HR Team. I have the master database for all the previous and current employees. 

So If two employees with the same name join, I have to add 1 to the last name and so on...up to 10.


Can I use Vlookup and write a condition to look for the existing email address and write a new one... 


Support me if you have already solved the above challenge

3 Replies
best response confirmed by imsureshbio1109 (Occasional Contributor)

@imsureshbio1109 Without making it overly complicated I would introduce two helper columns. One that joins the the First and Last names and one that counts the occurrences of these names in the rows above. Have a look at the attached file for an example.


Firstly, you will need a uniquely assigned staff number.  Otherwise, you will not know which way round to assign the email addresses even when you have them.  VLOOKUP is not good enough because it will only detect a single instance of a matching name.

=  LET(
   base,     first & "." & last,
   number,   COUNTIFS(first,first, last,last, staffNum,"<"&staffNum),
   modifier, IF(number, number, ""),
   email,    base & modifier & "@" & domain,

This is for 365; legacy solutions will look very different and often require an understanding of mixed referencing.

@Riny_van_Eekelen Thanks Riny. This will do the trick