CODEWITHSUNDEEP
excelexcel-formula
Michael
Michael

Reputation: 127

Creating a reverse manager hierarchy in Excel

I have two columns, employee ID and Supervisor ID, in a format below:

Employee ID Supervisor ID
11111 22222
22222 33333
33333 44444
44444 55555
55555

Through VLOOKUPs or XLOOKUPs I can create a hierarchy that tell me who the top level manager is, but the top level manager doesn't always line up.

Employee ID Supervisor ID Supervisor+1 ID Supervisor+2 ID Supervisor+3 ID Supervisor+4 ID
11111 22222 33333 44444 55555
22222 33333 44444 55555

I am wanting to create a reverse hierarchy in Excel.

Employee ID Supervisor+4 ID Supervisor+3 ID Supervisor+2 ID Supervisor+1 ID Supervisor ID
11111 55555 44444 33333 22222
22222 55555 44444 33333

I read this article https://community.powerbi.com/t5/Power-Query/Reverse-Company-Hierarchy/td-p/1545861 where OP said they are able to complete it with MATCH and INDEX functions. But after reading about MATCH and INDEX functions, I am still not understanding. Any guidance would be appreciated!

Upvotes: 2

Views: 812

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34245

You could try a lambda like this:

=LAMBDA(employee,empRange,bossRange,LET(boss,XLOOKUP(employee,empRange,bossRange,""),IF(boss="","",getBoss(boss,empRange,bossRange)&"|"&boss)))

enter image description here

enter image description here

Upvotes: 2

Related Questions