Complex lookup between two spreadsheets

wohlfy

New Member
Joined
Oct 17, 2006
Messages
2
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

daveatthewell

New Member
Joined
Jul 28, 2006
Messages
43
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Yes, the Manager ID is a subset of the Employee ID column.

Sheet1
EmpMgr.xls
ABCD
1Emp Last NameEmp First NameEmp IDMgr ID
2FDAAbe1e2
3CDADebe2
4XZAFrae3e4
5CVATae4
Sheet1


Sheet2
EmpMgr.xls
ABCDE
1
2XZAFra3CVATa
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))
 

Forum statistics

Threads
1,136,650
Messages
5,677,000
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top