Help combining information from 2 sheets

[snapitsports]

My hobby business is race photography. We go out and shoot races and then upload them to our servers and then using an excel sheet with first name, last name, bib (race) number, and email address...we are able to upload this into the software to send out automated emails to each participant.

A few times now, I have gotten a file (sometimes 2) that have all this information but not all on the same and definitely not the same amount of entries on each as the email list only lists those that gave emails and not everyone else.

So I have tried to find a way to create a formula that would look at first name and last name and compare it to the other sheet and, if the same, it would copy over the email address to the field...otherwise leave it blank or put N/A or something.

Can someone please help?

Sheet 1 looks like this...
[TABLE="width: 500"]
<tbody>[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]User[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Susan[/TD]
[TD]Doe[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]John[/TD]
[TD]Williams[/TD]
[/TR]
</tbody>[/TABLE]


And then Sheet 2 looks like this...

[TABLE="width: 500"]
<tbody>[TR]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]john.doe@gmail.com[/TD]
[/TR]
[TR]
[TD]User[/TD]
[TD]Name[/TD]
[TD]user.name@gmail.com[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Williams[/TD]
[TD]john.williams@gmail.com[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And of course, I need it to combine to be...

[TABLE="width: 500"]
<tbody>[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]john.doe@gmail.com[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]User[/TD]
[TD]Name[/TD]
[TD]user.name@gmail.com[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Susan[/TD]
[TD]Doe[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]John[/TD]
[TD]Williams[/TD]
[TD]john.williams@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]

Hope this is pretty cut and dry. Thanks for any help you can shoot me!

 

[sergioMabres]

You would like to use vlookup and iserror
Using lastname&", "&name look into sheet2 and get back the email
Sergio