# Comparing Lists of Names and Employee #s

#### Merrie419

##### New Member
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?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

##### MrExcel MVP
Merrie419 said:
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

##### New Member
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

##### MrExcel MVP
Merrie419 said:
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
ABCDE
1
2A Hill13579John Doe1234512345
3A Smith14499B Hill1798117981
4B Hill17981A Smith1449914499
5C Hill12246
6Jane Doe12346
7John Doe12345
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),"")

Replies
4
Views
162
Replies
6
Views
406
Replies
5
Views
145
Replies
3
Views
294
Replies
10
Views
608

1,195,924
Messages
6,012,331
Members
441,690
Latest member
CyberWrek

### 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?

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