If Formula question

F

Fury

I'm trying to crossmatch 2 files... the first... a list of about 15
employee's who are being terminated (150 rows... 4 Columns: last name
first name, dept., status).

The second, a list of all employee's (approx. 600) that has about 2
columns of necessary information, including the column of informatio
i'm trying to get for the first file.

now... if the employee's name is on the first file... i need to be abl
to pull the information that is in the cell of the "status" column o
the second file.

I've tried creating a column in each file with the concatenate functio
(Last,First,Department), to get "True" if it was on the first file...
but.. am not sure if i'm heading in the right direction... I'm new t
IF Formulas, but am sure that there would be an easy way to do this...



Please Help
 
F

Frank Kabel

Hi
one way (if col. F is your status column and column A+B stores name +
first name)
Enter the following array formula (with CTRL+SHIFT+ENTER) in E1
=INDEX('employee'!$F$1:$F$1000,MATCH(1,('employee'!$A$1:$A$1000=A1)*('e
mployee'!$B$1:$B$1000=B1),0))

copy down
 
A

Anders S

Assuming there is a unique identifier for each employee in both tables, VLOOKUP
is the way to go IMO.

It will look something like
=VLOOKUP(A1,Sheet2!A1:A30,2)
with the ID's in column A on both sheets, and the status column in column B on
Sheet2.

HTH
Anders Silven
 
Top