search one worksheet and copy data to another

Microsoft Excel 2010
May 17, 2011 at 08:54:18
Specs: Windows 7
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?

See More: search one worksheet and copy data to another

Report •

#1
May 17, 2011 at 14:30:42
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   567

On 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 down

On 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

http://www.skeptic.com/


Report •
Related Solutions


Ask Question