Excel help

[jayesh]

I have list of employees and their department. Column A contains the Department Name, B has name C has address D has phone and E has email.
There is a list of 200 of them.
Is there a way that if I type dept name in other sheet and i can get the list of all the employees with all the column B thru E?

 

[jayesh]

One more request. I know I am demanding a lot.
Can we add one more condition. If I add a column of region. Can I get regionwise and dept.wise result?

 

[texasalynn]

you would change the formula to include the additional column ended. So the $F$22 would be changed to last column letter H

=IF(ROWS(A$3:A3)>$C$1,"",INDEX(Open!$A$1:$F$22,SMALL(IF(Open!$A$1:$A$22=$B$1,ROW(Open!$A$1:$A$22)),ROW(1:1)),COLUMNS($A2:A2)))