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)