I posted this question earlier but this is a simplified version of it.

In column A I have employees' names, in column B I have Team Leaers' names.

Employee names will be unique in each cell but the team leaders names will
be repeated several times.

something like

Employee TL

John Robert
Kelly Robert
Michael Robert
Gary Daniel
Jerry Daniel
Jim Robert
Brian Robert
Richie Kevin
Pete Kevin
Alex Daniel


Now if I enter the Team Leader's name in another sheet's column A and drag
it down, I want the names of the employees (only the ones in his team) to
return in column B.

Possible?

Thanks.

In Sheet 2

=IF(ISERR(SMALL(IF(TL=Sheet2!$A$2,ROW(INDIRECT("1: "&ROWS(TL)))),ROWS($1:1))),"",INDEX(Employee,SMALL (IF(TL=Sheet2!$A$2,ROW(INDIRECT("1:"&ROWS(TL)))),R OWS($1:1))))

or

=IF(COUNTIF(TL,$A$2)=ROWS($1:1),INDEX(Employee,SM ALL(IF(TL=$A$2,ROW(TL)-MIN(ROW(TL))+1),ROWS($1:1))),"")

Both of formulas above are required with ctrl+shift+enter, not just enter
copy down as far as needed.


"Gary" wrote:

I posted this question earlier but this is a simplified version of it.

In column A I have employees' names, in column B I have Team Leaers' names.

Employee names will be unique in each cell but the team leaders names will
be repeated several times.

something like

Employee TL

John Robert
Kelly Robert
Michael Robert
Gary Daniel
Jerry Daniel
Jim Robert
Brian Robert
Richie Kevin
Pete Kevin
Alex Daniel


Now if I enter the Team Leader's name in another sheet's column A and drag
it down, I want the names of the employees (only the ones in his team) to
return in column B.

Possible?

Thanks.

Try this.

Sheet1 A2:A11 = employee names
Sheet1 B2:B11 = team leader names

Sheet2 A1 = any one of the team leaders names

Enter this array formula** in Sheet2 A2:

=IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!B$2:B$11,A$1),IND EX(Sheet1!A$2:A$11,SMALL(IF(Sheet1!B$2:B$11=A$1,RO W(Sheet1!A$2:A$11)-MIN(ROW(Sheet1!A$2:A$11))+1),ROWS(A$2:A2))),"")

Copy down until you get blanks. You need to copy to enough cells that covers
the max number of employees for any single leader. For example, in your
sample Robert is listed the most, 5 times, so you need to copy the formula
to at least 5 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Gary" wrote in message
...
I posted this question earlier but this is a simplified version of it.

In column A I have employees' names, in column B I have Team Leaers'
names.

Employee names will be unique in each cell but the team leaders names will
be repeated several times.

something like

Employee TL

John Robert
Kelly Robert
Michael Robert
Gary Daniel
Jerry Daniel
Jim Robert
Brian Robert
Richie Kevin
Pete Kevin
Alex Daniel


Now if I enter the Team Leader's name in another sheet's column A and drag
it down, I want the names of the employees (only the ones in his team) to
return in column B.

Possible?

Thanks.