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.