I want to search one worksheet for employee ID (column named employee ID) and place that number in the blank employee ID column on the 2nd worksheet based on employee name. The first and last names are in separate columns on both worksheets. Both first AND last names must match in order to copy the employee id number. Is this even possible in Excel?

There are two ways you can do this, and it's dependendant on how your ID number is configured.

If it is ALL numbers then a =SUMPRODUCT() function can get the data

If is a mix of numbers and Text or all Text then the =VLOOKUP with a helper column should work.If your data looks like this:

=SUMPRODUCT() - ID's must be numbers.

Sheet 1 A B C 1) Last First ID 2) Public John 123 3) Jones David 234 4) Smith Joe 345 5) Mouse Mickey 456 6) Mouse Minnie 567On Sheet 2

Enter the Last Name in cell A1

Enter the First Name in cell B1

In cell C1 enter the formula:=SUMPRODUCT((Sheet1!$A$1:$A$6=A1)*(Sheet1!$B$1:$B$6=B1)*(Sheet1!$C$1:$C$6))

With =VLOOKUP() you will need to insert a helper column before column A

so your data looks like this:A B C D 1) Last First ID 2) PublicJohn Public John jp01 3) JonesDavid Jones David dj02 4) SmithJoe Smith Joe js03 5) MouseMickey Mouse Mickey mm04 6) MouseMinnie Mouse Minnie mm05

In Column A enter the formula =B2&C2 and drag downOn Sheet 2

Enter the Last Name in cell A1

Enter the First Name in cell B1

In cell C1 enter the formula:=VLOOKUP(A1&B1,Sheet1!A2:D6,4,FALSE)

See how that works.MIKE

Ask Your Question

Weekly Poll