One play using non-array formulae which would "auto-extract" the employee
ids into each dept's sheet from a master list ..

A sample implementation is available at:
http://cjoint.com/?lydMDFJAhn
AutoSortEmployee_InTo_Dept_SheetName.xls

Assume the master list is in sheet: Master
in cols A to B, headers in row1, data from row2 down

Using empty cols to the right of the data, say cols K onwards
List the Dept names in K1, L1 across: Mktg, Admin, Acct, etc
Put in K2: =IF($B2=K$1,ROW(),"")
Copy K2 across to M2, fill down to say M20,
to cover the max expected data in the master list

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan)

In a new sheet named: Mktg
With the same col headers in A1:B1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,M aster!$K$1:$IV$1,0)),ROWS(
$A$1:A1))),"",INDEX(Master!A:A,MATCH(SMALL(OFFSET( Master!$J:$J,,MATCH(WSN,Ma
ster!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Master!$ J:$J,,MATCH(WSN,Master!$K$
1:$IV$1,0)),0)))

Copy A2 across to B2, fill down to B20
(cover the same range size as was done in "Master"' cols K, L)

Cols A to B will return only the lines for the Dept: Mktg from "Master",
with all lines neatly bunched at the top

Now, just make a copy of the sheet: Mktg, rename it as: Admin, and you'd get
the results for Admin. Repeat the sheet copy & rename as required to
extract the lists for the rest of the depts (a one-time effort).
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"thomasNg" wrote in
message ...

dear forum,

help please.
i have 2 columns in sheet 1: col A is employee ID, col B is Dept name.
i want to search col B, match it with the employee ID in col A.
i have several other sheets so that each sheet shows only the employee
IDs for one particular dept.

so far i have used a simple if(dept="marketing",a1,"--"). i am not too
sure how to use vlookup in this case. also i dont want to skip rows.

this is what i am getting with my formula
sheet 1 sheet 2
sheet 3
1012 Acct --
1012 acct
1064 Marketing 1064 marketing
--
2066 Admin --
--
5066 Acct --
5066 acct
6654 Marketing 6654 marketing
--

sorry this post is longwinded, but i thought i'd give as much b/ground
info as possible.

thanx

thomas.


--
thomasNg
------------------------------------------------------------------------
thomasNg's Profile:
http://www.excelforum.com/member.php...fo&userid=8375
View this thread: http://www.excelforum.com/showthread...hreadid=487834