Help looking up multiple values using one key. VLOOKUP? INDEX? MATCH?

[servcoor]

Hello. I appreciate your help on this:

Cell A1 contains the name of a supervisor. Cells in A2:A20 need to be populated with the names of the supervisor's employees. A sheet in the same workbook named All Teams contains a table listing all supervisors and their employees columnar format. The supervisor's names all appear in Column A and their employees' names are found in Column F. So if the supervisor has 15 employees, the supervisor's name will be listed in Column A in 15 consecutive rows. Due to hiring/terminations/team reorganizations, this table changes frequently so the supervisor's names do not always land in the same rows when the sheet is update. What formula in A2:A20 can be used to return each of the supervisor's employee's names whenever the table on the All Teams sheet is updated? VLOOKUP alone doesn't do it. Thank you.

 

[Stridhan]

in A2 and enter with ctrl+shift+enter, not just enter
adjust sheet2 range in all places to fit your list in sheet2

=IFERROR(INDEX(Sheet2!$F$2:$F$20,SMALL(IF(Sheet2!$A$2:$A$20=B$1,ROW(Sheet2!$A$2:$A$20)-ROW($A$2)+1),ROWS($A$2:B2))),"")

 

[servcoor]

Stridhan, sorry for the delay in responding to your suggested solution. It worked. It took me a few attempts to figure out how to get it to work properly and to be able to modify it for several different sections of my worksheet but I've got it performing the way it should. I can't say I understand how it works, but it works and does exactly what I needed it to.

Thank you.

 

[Stridhan]

You're welcome