Matching data and returning header

I have a list of Employees and I want to lookup and copy each employee's
department into the next field. Right now I have it setup on two spread
sheets. One spread sheet has the list of employees as part of an
inventory.

*Employee Deparment*
Employee
Employee
Employee
Employee
Employee
Employee
Employee

The other spreadsheet has all the departments and the respective
employees in columns.


DEPARTMENT 1 DEPARTMENT 2 DEPARTMENT 3
Employee 1 Employee 1 Employee 1
Employee 2 Employee 2 Employee 2
Employee 3 Employee 3 Employee 3
Employee 4 Employee 4 Employee 4
Employee 5 Employee 5 Employee 5
Employee 6 Employee 6 Employee 6
Employee 7 Employee 7 Employee 7

How can I make the first spreadsheet reference the second one to fill
in the department column with the headers from the respective columns?

Thanks for any help!


---
Message posted from http://www.ExcelForum.com/

This is a BUSY forum. I can't believe I already need a *bump*

This seems like a simple problem. Does anyone even have a guess?


---
Message posted from http://www.ExcelForum.com/

matman17 wrote:
*I have a list of Employees and I want to lookup and copy each
employee's department into the next field. Right now I have it setup
on two spread sheets. One spread sheet has the list of employees as
part of an inventory.

Employee Deparment*
Employee
Employee
Employee
Employee
Employee
Employee
Employee

The other spreadsheet has all the departments and the respective
employees in columns.


DEPARTMENT 1 DEPARTMENT 2 DEPARTMENT 3
Employee 1 Employee 1 Employee 1
Employee 2 Employee 2 Employee 2
Employee 3 Employee 3 Employee 3
Employee 4 Employee 4 Employee 4
Employee 5 Employee 5 Employee 5
Employee 6 Employee 6 Employee 6
Employee 7 Employee 7 Employee 7

How can I make the first spreadsheet reference the second one to fill
in the department column with the headers from the respective
columns?

Thanks for any help!

Hi,

1) Assuming your list of employees and their respective departments are
in Sheet 2, and start in Row 2, and

2) Assuming that your list of employees are in Column A of Sheet 1, and
starts in Row 2,

put the following formula in B2 of Sheet 1 and copy down:

=INDEX(Sheet2!$A$1:$C$1,MAX(IF(Sheet2!$A$2:$C$8=A2 ,COLUMN(Sheet2!$A$2:$C$8))))

entered using CTRL+SHIFT+ENTER

Hope this helps!


---
Message posted from http://www.ExcelForum.com/