Matching data and returning header

M

matman17

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

*Employee Deparment*
Employee
Employee
Employee
Employee
Employee
Employee
Employee

The other spreadsheet has all the departments and the respectiv
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 fil
in the department column with the headers from the respective columns?

Thanks for any help
 
M

matman17

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
 
D

Domenic

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

Employee Deparment*
Employee
Employee
Employee
Employee
Employee
Employee
Employee

The other spreadsheet has all the departments and the respectiv
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 fil
in the department column with the headers from the respectiv
columns?

Thanks for any help!
Click to expand...

Hi,

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

2) Assuming that your list of employees are in Column A of Sheet 1, an
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
 
Top