Vlookup or Index or other?

[lgonzales]

Hello,

I am seeking some help with a problem I am having. I have one Excel Workbook with 2 sheets.

Sheet 1 has a list of all active employees. Sheet contains columns for FName and LName. Total rows = 935

Sheet 2 has a list of employees with an email address. Sheet contains columns for FName, LName and Email Address. Total rows = 450.

Need help to compare both sheets. If FName and LName on Sheet 1 are found on Sheet 2 then have email address entered into column on Sheet 1.

Any assistance is appreciated.

Thank you,
Leo

 

[Eric W]

Maybe:

Excel 2010

	A	B	C
John	Doe	email@msn.com
Jane	Smith
Mark	Trail	email2@yahoo.com
Sandra	Dee
Frank	Jones	email3@gmail.com

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=IFERROR(INDEX(Sheet2!C:C,1/(1/SUMPRODUCT(--(Sheet2!$A$1:$A$500=$A1),--(Sheet2!$B$1:$B$500=$B1),ROW($B$1:$B$500)))),"")&""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2010

	A	B	C
John	Doe	email@msn.com
Mark	Trail	email2@yahoo.com
Frank	Jones	email3@gmail.com

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

Put the formula in C1 on sheet 1 and copy it down. Change the range references to match your sheet.

This requires Excel 2010 or later. It would also return an incorrect email address if Sheet2 has more than 1 address for a given name.

Let me know how that works.

 

[Aladin Akyurek]

In B1 of Sheet1 control+shift+enter, not just enter, and copy down:

=IFERROR(T(INDEX(Sheet2!$C$1:$C$30,MATCH($B1,IF(Sheet2!$A$1:$A$30=$A1,Sheet2!$B$1:$B$30),0))),"")

If you have to many records to check, IFERROR can be replaced for more speed.

 

[lgonzales]

Thank you. Worked Perfectly!!!

 

[Eric W]

Glad we could help.

 

[Aladin Akyurek]

Thank you. Worked Perfectly!!!

You are welcome.

Glad we could help.

Indeed. A side note though: Invoking SumProduct can constitute a risk in a retrieval (look up) task.