extract Employee ID by matching name and email address

[Psoni]

Hello ALL,I have two sheets-Sheet1 has Last name,First name in one column and email address in one column.Sheet2 also has Last name, First name in one column, email address in one column and Employee id in another.I want a macro to match names from sheet 1 with two columns - Last name, First name and also email address if both match than i wanted to extract Employee id and write adjacent to that name on Sheet 1ThanksPuneet

 

[hiker95]

Psoni,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

We can not tell where your raw data is located, cells, cell formatting, rows, columns, and, we can not tell where the results should be, cells, cell formatting, rows, columns.

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[alansidman]

On both sheets, insert a column to the left of the first column. In that column (Assume it is Col A), type = B1 & C1 and then copy down.
In column D of the sheet where you want to add the ID. Type =vlookup(A1, Sheet2!$A$1:$D$1000,4,False) and copy down. This assumes the ID is in Column D and there are a 1000 entries. Change as needed.

 

[Psoni]

Hi Alan,
Thanks for your help, but for some reason i am not getting the desired results. May be i am not able to explain what i am looking for. Here is my 2 sheets:

Sheet1


Column J K L


Employee_ID Employee_Name Employee_Email


Tatiana Ilescu Tatiana.Ilescu@xyz.com
Virginie Cote Virginie.Cote@xyz.com
VIVIAN GARCIA VIVIAN.GARCIA@xyz.com


Sheet2


Column C F M


Employee_ID Employee_Name Employee_Email


315682 Tatiana Ilescu Tatiana.Ilescu@xyz.com
418250 Virginie Cote Virginie.Cote@xyz.com
615289 VIVIAN GARCIA VIVIAN.GARCIA@xyz.com


All i am looking for is match name and email from sheet2 and extract employee_ID to paste on Sheet1

 

[strooman]

Formula Sheet1 J2:

=IFERROR(INDEX(Sheet2!$C$2:$C$4;MATCH(K2&L2;Sheet2!$F$2:$F$4&Sheet2!$M$2:$M$4;0));"")

Insert with Ctrl+Shift+Enter. Not just ENTER

Sheet1

Row\Col	J ​	K​	L ​
1​	Employee_ID	Employee_Name	Employee_Email
2​	315682 ​	Tatiana Ilescu	Tatiana.Ilescu@xyz.com
3​	418250​	Virginie Cote	Virginie.Cote@xyz.com
4​	615289​	VIVIAN GARCIA	VIVIAN.GARCIA@xyz.com

<tbody>
</tbody>

Sheet2

Row\Col	C ​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M ​
1​	Employee_ID			Employee_Name							Employee_Email
2​	315682​			Tatiana Ilescu							Tatiana.Ilescu@xyz.com
3​	418250​			Virginie Cote							Virginie.Cote@xyz.com
4​	615289​			VIVIAN GARCIA							VIVIAN.GARCIA@xyz.com

<tbody>
</tbody>