to return Mutliple results with one search item

Hi,

I am working on building a schedule for my department. The department I work in is comprised of four sub-departments. Each has their own schedule that is then compiled into one master schedule. I am trying to find ways to save some time. I have looked all over this site for the answer and have found a few things that seem to be working but aren't returning the proper results. I am not sure why. I was hoping for some help.

I have included a dummy spreadsheet.

The idea is to have the first page be the master schedule then four successive pages with each departments individual schedule. The example that I have included has the formula I have been working with and search located at the top of the first page. This is only for testing; to see if I was getting the proper results before I began moving it to the other pages.

While I realize that a simple filter or a pivot table might be the answer, I work with a large group of people that do not want to (or have the ability to figure out) the buttons. Therefore I need something to populate the lists in an easier form than by hand.

Exp.

Sheet 1
Colum A Colum B
Dept Emp. Name Monday Tuesday

AV John Smith 9a - 5p 9a - 5p
Carp Bob Smith 10p - 1a Off
Audio Jane Doe 3p - 11p 8p - 12a
Elec Joe johnson 4p - 8p 3p - 7p


These examples then would break out to sheet 2,3,4,5, respectively.
I have been working with
=INDEX(name,SMALL(IF((dept=$A$3),ROW(dept)),ROW()))
However it is not returning the proper information.
Any help would be appreciated. Thanks.

You need to adjust for postions...try ammending to:

=INDEX(name,SMALL(IF((dept=$A$3),ROW(dept)-ROW(OFFSET(dept,,,1,1))+1),ROWS($A$1:$A1)))