Sequential vlookup for a group? Supervisors and their staff

CtrlAltDelete

New Member
Joined
Sep 21, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

1632242428721.png
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What columns are the staff names & supervisor names? Also what is the sheet called?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,829
Welcome to the forum!

Try:

Book1 (version 1).xlsb
ABCD
1WorkerSuperviserSupervisor
2ZekeJimmy PestoBob Belcher
3Jimmy Jr.Jimmy Pesto
4TinaBob Belcher
5GeneBob BelcherDirect Reports
6TeddyJimmy PestoTina
7LindaBob BelcherGene
8LouiseBob BelcherLinda
9Louise
10
Sheet28
Cell Formulas
RangeFormula
D6:D9D6=FILTER(Sheet28!A2:A10,Sheet28!B2:B10=D2)
Dynamic array formulas.
 

CtrlAltDelete

New Member
Joined
Sep 21, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

CtrlAltDelete

New Member
Joined
Sep 21, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum!

Try:

Book1 (version 1).xlsb
ABCD
1WorkerSuperviserSupervisor
2ZekeJimmy PestoBob Belcher
3Jimmy Jr.Jimmy Pesto
4TinaBob Belcher
5GeneBob BelcherDirect Reports
6TeddyJimmy PestoTina
7LindaBob BelcherGene
8LouiseBob BelcherLinda
9Louise
10
Sheet28
Cell Formulas
RangeFormula
D6:D9D6=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,955
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Sep 21, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,836
Messages
5,766,716
Members
425,373
Latest member
ndiejennrrd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top