Search code examples
excellambdaexcel-365

Does the excel "LET" function perform lazy evaluation - are unused name value pairs actually evaluated

Suppose you have the following formula:

LAMBDA(returnVar_,
LET(
  let_var1, XLOOKUP(...),
  let_var2, XLOOKUP(...),
  delta, let_var1 - letvar2,
  SWITCH(returnVar_,
         "let_var1", let_var1,
         "let_var2", let_var2,
         "delta", let_var1 - letvar2))("let_var1")

Does excel evaluate "let_var2" and "delta" in such a scenario or is frugal/efficient to only evaluate "let_var1", "let_var2" or "delta" as needed?

I know based on the below microsoft reference that "let" is efficient to not evaluate items more than once if not needed. However I cannot find information on whether it evaluate it at least once in all cases:

https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999

Solution

  • Thanks @GSerg, @BigBen, @DS_London. All of your comments point me in the right direction to test the behaviour.

    I used the comments to make some tests of let, switch and if functions with the MMULT calculation/data generation and the behaviour is as follows based on the observed performance:

    Support functions:

    fn_MMULTData =LAMBDA(n,MMULT(RANDARRAY(n, n), RANDARRAY(n, n)))

testLet = LAMBDA(_returnVar,LET(aPlusb, 1 + 1, c, fn_MMULTData(1000), aPlusb))

testSwitch = LAMBDA(_returnVar,SWITCH(_returnVar, "aPlusb", 2, "c", fn_MMULTData(1000)))

testIF = LAMBDA(_returnVar,IF(_returnVar = "aPlusb", 2, fn_MMULTData(1000)))

    The order of statements did not matter, nor did wrapping things in a helper LAMBDA except for the IF statement. Its performance didn't degrade due to evaluation of fn_MMULTData(1000) when it matched the condition. Maybe someone has a connection at Microsoft.

    Edit 1: CHOOSE seems combined with switch might be an elegant way of achieving the desired:

    =LET(_returnVar, "1Plus1",
_returnVarIdx, SWITCH(_returnVar, "1plus1", 1, 2),
CHOOSE(_returnVarIdx, 1+1, fn_MMULTData(1000)))

    Edit 2: CHOOSE works also irrespective of order of evaluation

    =LET(_returnVar, "1Plus1",
_returnVarIdx, SWITCH(_returnVar, "1plus1", 2, 1),
CHOOSE(_returnVarIdx, fn_MMULTData(1000), 1+1))