Forum Discussion
SWITCH and IFS - a word to the wise
Last night my Excel very nearly died executing something that looked perfectly reasonable:
myLambda = LAMBDA(i, SWITCH(i, 1, ..., 15, LET( ..., l, myLambda(1), ...))
There is a recursion but it wasn't supposed to end in disaster because branch 15 would not be invoked second time around. Turns out, it nearly ended in disaster because Excel apparently evaluates ALL the branches of the SWITCH before making a decision.
This was totally unexpected and there isn't a word of warning in Microsoft's online documentation. It is also totally contrary to intuition. SWITCH will only ever be deployed in a scenario where there are
. a large number of branches which, even if each branch were easy to evaluate, would in their multitude still and always impose a significant computational overhead; and
. no more than one condition ever applies (such that n-1 evaluations are assuredly a wasted effort - or worse, as in my example).
My next port of refuge was IFS. There is an argument anyway that SWITCH is redundant in the presence of IFS. IFS offers greater flexibility and the overhead of typing out IFS over SWITCH does not really move the needle.
Alas, same thing again !!
These new functions are incredibly poorly implemented. With regret, I must return to nested IFs. IF only evaluates the applicable branch. Only problem is keeping track of the number of closing brackets....
It's sad. See for example Excel IFS, SWITCH, and XLOOKUP evaluate all their arguments
HansVogelaarWould upLike your post but that doesn't seem to work today. Your link is a better write-up than mine 🙂 It is outrageous, really, because if Microsoft wanted to be lazy, they would only need to teach the precompiler to translate SWITCH and IFS into IFs in the first pass (a doddle for the precompiler which does not mind counting brackets) and then get the expected functionality for free in the second.
