Help with formula(s) to retrieve information

EDIT: SOLVED


Hello,

So, I initially thought this would be a simple process. I have two sheets, one with a list of Manager First (column A), Manager Last (column B), and Manager Email (column C). The second sheet has data, and then eventually, Manager First (column X), Manager Last (column Y), and Manager Email (column Z). The manager First and Last are on the second sheet, but the email is not. The data on the second sheet is copy + pasted from a large report, and there are usually over 200 rows of data, making hand entering the manager email column quite a process. The reason the email is needed is that once I finish my information, another person parses the report and sends an email to the various managers about each individual row.

I can't use a standard VLookup because we have managers with the same last names.

I also cant concatenate their names in a hidden column because.. well, I could live with that, but my manager wants it to be fully automated.

So, I tried two different methods. I wrote a formula that was basically like this; =IF(AND(EMPLOYEES!X1=Managers!A:A,EMPLOYEES!X2=Managers!B:B),Managers!D:D,ERROR), and you can guess what I saw.

I then got crafty, and tried this.

=IF(AND(EMPLOYEES!X1=Managers!A:A,EMPLOYEES!X2=Managers!B:B),VLOOKUP((Employees!C2,Managers!C:D,2,FALSE),ERROR)

It worked for the first 4 times the manager name was found, then resulted in a #NAME? error.

I also tried slamming my head on the keyboard repeatedly. That also resulted in nothing useful.

Help me excelforum, you're my only hope!

help.xlsx

Hi
try

Please Login or Register  to view this content.

entered as an array formula with Ctrl+Shift+Enter

hi wpsang, welcome to the forum. i'm sure something will come up if you slam it several more times.
on a serious note, you have repeated names appearing. so do you want the emails to appear more than once? if so, try:
=INDEX(Managers!$D$2:$D$10,MATCH(B2&C2,INDEX(Managers!$B$2:$B$10&Managers!$C$2:$C$10,),0))

and if you need those who are not in the manager's list to show as blanks, this will work in Excel 2007 & above:
=IFERROR(INDEX(Managers!$D$2:$D$10,MATCH(B2&C2,INDEX(Managers!$B$2:$B$10&Managers!$C$2:$C$10,),0)),"")