Sequential vlookup for a group? Supervisors and their staff

[CtrlAltDelete]

Hi. I have a worksheet where each row lists a staff name and their supervisor. Some supervisors have multiple people reporting to them.

I am wondering if it's possible to do the following on a separate worksheet: When I type in the name of a supervisor, each of their direct reports (supervisees) appears one time on subsequent rows.

E.g., if Bob Belcher supervises Tina, Gene, Linda and Louise, then my goal would be that after typing "Bob Belcher" in one cell, each of his supervisees appears one time in the following rows as highlighted in the image. I know i can add filters to the reference sheet and see the supervisees that way but it adds an extra step to an already laborious process at hand. Appreciate any ideas.

 

[Fluff]

Hi & welcome to MrExcel.
What columns are the staff names & supervisor names? Also what is the sheet called?

 

[Eric W]

Welcome to the forum!

Try:

Book1 (version 1).xlsb
	A	B	C	D
1	Worker	Superviser		Supervisor
2	Zeke	Jimmy Pesto		Bob Belcher
3	Jimmy Jr.	Jimmy Pesto
4	Tina	Bob Belcher
5	Gene	Bob Belcher		Direct Reports
6	Teddy	Jimmy Pesto		Tina
7	Linda	Bob Belcher		Gene
8	Louise	Bob Belcher		Linda
9				Louise
10
Sheet28

Cell Formulas
Range	Formula
D6:D9	D6	=FILTER(Sheet28!A2:A10,Sheet28!B2:B10=D2)
Dynamic array formulas.

 

[CtrlAltDelete]

Hi & welcome to MrExcel.
What columns are the staff names & supervisor names? Also what is the sheet called?

Hi and thank you. The Reference sheet is called PAY RATES.

The columns are C and D, EMPLOYEE and SUPERVISOR respectively.

 

[Fluff]

Thanks for that, try Eric's formula & if you need help changing it to your data, let us know.

 

[CtrlAltDelete]

Welcome to the forum!

Try:

Book1 (version 1).xlsb
	A	B	C	D
1	Worker	Superviser		Supervisor
2	Zeke	Jimmy Pesto		Bob Belcher
3	Jimmy Jr.	Jimmy Pesto
4	Tina	Bob Belcher
5	Gene	Bob Belcher		Direct Reports
6	Teddy	Jimmy Pesto		Tina
7	Linda	Bob Belcher		Gene
8	Louise	Bob Belcher		Linda
9				Louise
10
Sheet28

Cell Formulas
Range	Formula
D6:D9	D6	=FILTER(Sheet28!A2:A10,Sheet28!B2:B10=D2)
Dynamic array formulas.

Thank you, I just tried that but I'm getting a Spill response. My reference columns are from the PAY RATES worksheet and are columns C and D for Employee and Supervisor accordingly.

I entered the following formula in the first cell where i'd like the direct reports to appear.

=FILTER('PAY RATES'!C2:C145,'PAY RATES'!D2:D145=D8)

 

[Fluff]

A #Spill error, means that there are non-blank cells in the way of the formula.
Try deleting the contents of the cells below the formula.

 

[CtrlAltDelete]

Thank you, I just tried that but I'm getting a Spill response. My reference columns are from the PAY RATES worksheet and are columns C and D for Employee and Supervisor accordingly.

I entered the following formula in the first cell where i'd like the direct reports to appear.

=FILTER('PAY RATES'!C2:C145,'PAY RATES'!D2:D145=D8)

Never mind! I just needed to delete the next row. THANK YOU SO MUCH! WOW. Excel amazes me. You all are great. I will note this formula for future use.

 

[Eric W]

Glad we could help.