Complex lookup between two spreadsheets

[wohlfy]

I have two spreadsheets and need to perform a complex lookup.

One sheet contains:
Employee Last Name
Employee First Name
Employee ID
Manager ID
Director ID

The second sheet contains:
Employee Last Name
Employee First Name

I have been tasked with trying to come up with a way to use the Employee Last Name and possibly the Employee First Name from the second sheet to locate that same employees name on the first sheet then get the corresponding Manager ID for that employee from the first sheet and search for the Manager ID in the Employee ID column on the first sheet to obtain the First and Last name of the manager and fill cells on the second sheet with the manager first and last name. Anyone have any ideas? I am familiar with VLOOKUPs but I have not found a way to perform this operation.

 

[Aladin Akyurek]

So Manager ID's are a subset of Employee ID's, right?

 

[wohlfy]

Yes, the Manager ID is a subset of the Employee ID column.

 

[daveatthewell]

There is no sure fire way of solving this as it stands. I deal with this problem on a daily basis - developers seem to think it's great to use first and second names in databases to pull stats - because it's "better" to say "Hi Dave Kennedy, you've scored ten out of ten" than "Hi, KennedyD, you've scored ten out of ten" ignoring the fact that there could be a hundred Dave Kennedy's in the database!
As long as sheet 2 contains employee ID and sheet 1 contains First name, last name, employee ID, where employee ID is unique then there is not a problem and vlookups will work. Trust me.

 

[Aladin Akyurek]

Yes, the Manager ID is a subset of the Employee ID column.

Sheet1

EmpMgr.xls
	A	B	C	D
1	Emp Last Name	Emp First Name	Emp ID	Mgr ID
2	FDA	Ab	e1	e2
3	CDA	Deb	e2
4	XZA	Fra	e3	e4
5	CVA	Ta	e4
Sheet1

Sheet2

EmpMgr.xls
	A	B	C	D	E
1
2	XZA	Fra	3	CVA	Ta
3
4
Sheet2

C2:

=MATCH(1,IF(Sheet1!$A$2:$A$5=A2,IF(Sheet1!$B$2:$B$5=B2,1)),0)

which must be confirmed with control+shift+enter, not just with enter.

D2, copied across to E2:

=INDEX(Sheet1!A$2:A$5,MATCH(INDEX(Sheet1!$D$2:$D$5,$C2),Sheet1!$C$2:$C$5,0))