Comparing Lists of Names and Employee #s

[Merrie419]

Hi- I was searching the board and see a lot of useful information, however it doesn't seem to answer my question.

I have a master list of employee names and ID numbers which is about 1800 rows -the data is in Columns A&B. I also have a smaller list of employees without ID numbers - This list is in Column C- The employees in the smaller list should be included in the 1800. How do I compare C to A&B and then paste the ID numbers of Column C Employees in Column D?

 

[Aladin Akyurek]

Hi- I was searching the board and see a lot of useful information, however it doesn't seem to answer my question.

I have a master list of employee names and ID numbers which is about 1800 rows -the data is in Columns A&B. I also have a smaller list of employees without ID numbers - This list is in Column C- The employees in the smaller list should be included in the 1800. How do I compare C to A&B and then paste the ID numbers of Column C Employees in Column D?

If A:B is sorted in ascending order on column A...

=IF(LOOKUP(C2,A:A)=C2,LOOKUP(C2,A:B),"")

Otherwise:

=IF(ISNUMBER(MATCH(C2,A:A,0)),VLOOKUP(C2,A:B,2,0),"")

 

[Merrie419]

Thanks for the speedy response - I am trying your suggestions, My data in column A is sorted in Ascending order and I am pasting the formula in column D - I am getting blanks in all of those cells - Not sure why this is happening - the formulas aren't working for me - Any Suggestions?

Just in case my description was not clear here is how my data is structured

Column A Column B Column C D
Master Employee List Master Employee ID List Partial Employee List

John Doe 12345 John Doe
Jane Doe 12346 B Hill
A Hill 13579 A Smith
B Hill 17981
C Hill 12246
A Smith 14499

 

[Aladin Akyurek]

Thanks for the speedy response - I am trying your suggestions, My data in column A is sorted in Ascending order and I am pasting the formula in column D - I am getting blanks in all of those cells - Not sure why this is happening - the formulas aren't working for me - Any Suggestions?

Just in case my description was not clear here is how my data is structured

Column A Column B Column C D
Master Employee List Master Employee ID List Partial Employee List

John Doe 12345 John Doe
Jane Doe 12346 B Hill
A Hill 13579 A Smith
B Hill 17981
C Hill 12246
A Smith 14499

Book4
	A	B	C	D	E
1
2	A Hill	13579	John Doe	12345	12345
3	A Smith	14499	B Hill	17981	17981
4	B Hill	17981	A Smith	14499	14499
5	C Hill	12246
6	Jane Doe	12346
7	John Doe	12345
8
Sheet1

If A:B is sorted on A in ascending order......

D2, copied down:

=IF(LOOKUP(C2,$A$2:$A$7)=C2,LOOKUP(C2,$A$2:$B$7),"")

If A:B is not sorted on A in ascending order...

F2, copied down:

=IF(ISNUMBER(MATCH(C2,$A$2:$A$7,0)),VLOOKUP(C2,$A$2:$B$7,2,0),"")