Need Help with Logic on Employee Level

viratsharma 

Hi,

I made the recursive lambda slightly shorter and removed “Switch” and “Let”, and keep only one core function (XLOOKUP) to make it easier to understand:

=LAMBDA(reports_to, level, IF(reports_to = "", level, LevelsOfPerson(XLOOKUP(reports_to, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1), level + 1)))

I understand you might not be very enthusiastic about coding, but I still think it is worth trying to explain the lambda as clearly as possible using the example data:

We have a customised function called “LevelsOfPerson”

In order to check the level of Associate1, whose direct report is Manager6

We use  LevelsOfPerson(reports_to=“Manager6”,  level = 1)

Here is how this function is executed:

First, it checks whether “Manager6” is an empty string or, apparently, it is not.

So LevelsOfPerson will be called again.

But this time, it is called with below parameters:

reports_to=“XLOOKUP(“Manager6”, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1) = “Leader3”

level = 1 + 1 = 2

That is, LevelsOfPerson(reports_to=“Leader3”, level=2) is being executed.

Since “Leader3” is still not an empty string, so LevelsOfPerson will be called another time, with different parameters:

reports_to=XLOOKUP(“Leader3”, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1) = “CEO”

level = 2+1 = 3

So LevelsOfPerson(reports_to=“CEO”,level=3) is being executed.

As “CEO” is still not an empty string, we will call LevelsOfPerson another time, with below parameters:

reports_to=XLOOKUP(CEO”, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1) = “”

level = 3 + 1 = 4

So LevelsOfPerson(reports_to=“”,level=4) is being executed.

Finally, “” is an empty string, level = 4 will be returned. (by below IF(reports_to = "", level, ……))

IF(reports_to = "", level, LevelsOfPerson(XLOOKUP(reports_to, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1), level + 1)

viratsharma 

For a technique that does not require helper columns (and can handle an arbitrary number of levels in the hierarchy), see the attached workbook.

Note: This uses the LAMBDA function, and thus requires Excel 365 or Excel for the web.

viratsharma 

Hi,

I think the ideal is just to find out how many layers of "Direct Report" an employee has to go through to reach "CEO".

e.g, Manager1, who reports to Leader2, who in turns reports to CEO, needs to go through 3 people to reach CEO, so Manager1's level is 3.

I implement it in Excel using some lookup formulas.