Auto Fill Emails based on Name Entry

[jwmartin]

Hello!

I'm working on a spreadsheet to distribute documents to clients via email. I have our company email list on one sheet of this workbook, and the list of people to which I'm distributing on another. Is there a formula or something that will automatically pull the correct email address from the first list, onto the second for each employee? Currently, I'm having to copy them one by one and it's a huge time sink. This is a recurring task, so I want to find ways to improve my efficiency and workflow.

I have the emails, first names, and last names all separated into different columns for both sheets to perform merge fields later on, but they could be combined if it makes the task easier.

[#2]

A vlookup will do it, but you'll need to change the order of your columns. The value you're looking to match (First Last) needs to be in the first column of your email database. I would also make a column where you combine first and last into a single column on both pages, it will make the lookup statement easier.

your formula will look like: vlookup(firstlast,emaildatabase,2,false)

[#3]

@NancyBogg There are multiple ways you can do this.  One way is to use XLOOKUP:

=XLOOKUP(TRIM([@[last_name]])&TRIM([@[first_name]]),TRIM(Table1[LastName])&TRIM(Table1[FirstName]),Table1[Email],"n/a")

I had to add the TRIM functions because some of the entries had extra spaces and others didn't.

If you don't have MS365 you could use INDEX( MATCH () ) combo instead:

=INDEX(Table1[Email],MATCH(TRIM([@[last_name]])&TRIM([@[first_name]]),TRIM(Table1[LastName])&TRIM(Table1[FirstName]),0) )