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!
