Forum Discussion
Does a LET variable get computed even if it's only referenced inside IFNA's fallback argument?
In the formula below, expensiveMatch is defined in LET but only referenced as IFNA's fallback argument:
=LET(
matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)),
x, INDEX(Sheet2!$C$1:$C$1000, matchRow),
expensiveMatch, INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)),
IFNA(x, expensiveMatch)
)Question: When x resolves successfully (no #N/A), does expensiveMatch still get computed because it's a top-level LET variable - or does Excel skip it since IFNA's fallback argument is never reached?
I know IFNA short-circuits its fallback argument, and LET avoids recomputing a variable if referenced multiple times - but I haven't found much documentation on the behavior of Let. Is the below a more efficient way to write the formula?
=LET(
matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)),
x, INDEX(Sheet2!$C$1:$C$1000, matchRow),
IF(ISNA(x),
INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)),
x
)
)Is this restructuring necessary, or does the first version already skip the unused computation?
Basically I'm wondering if Let computes lazily/defers calculation in the way SQL's optimizer works. Input from anyone with insight or knowledge into the calc engine would be greatly appreciated.
Thanks
(Currently on Microsoft Excel for Microsoft 365 MSO (Version 2607 Build 16.0.20228.20190) 64-bit)
2 Replies
- Patrick2788Silver Contributor
For this particular arrangement it's no hindrance. XMATCH works on 1D arrays and even with the concatenation it's still working on 1D.
Here's your formula arranged as a Lambda with exp_match deferred (thunked):
LamTestĪ»= LAMBDA( vector_1, crit_1, vector_2, crit_2, return_vector, LET( match_row, XMATCH(1, (crit_1 = vector_1) * (crit_2 = vector_2)), x, INDEX(return_vector, match_row), // Deferred exp_match, LAMBDA(INDEX(return_vector,XMATCH(crit_1 & crit_2,vector_1 & vector_2))), // Unwrap deferred exp_match if going to fall back final, IFNA(x,exp_match()), final ));To make things interesting, I extended your vectors down to row 10,000 and planted the matching row at row 7500.
The timings were close enough to be negligible.
Vector size Match Term Found 10k Row 7500 Regular Deferred (thunked) 0.11 0.14 0.11 0.15 0.15 0.15 0.14 0.11 0.13 0.14 0.128 0.138 There is a very small tax Excel charges for using LET as opposed to arranging your formula as a heavily nested-Excel 2016 style arrangement. An analogy I use: it's the weight of the bag when you're buying in bulk at the grocery store. At checkout, the weight of bag is subtracted when calculating the total. Sometimes you can recover this by using thunks.
For this example, that tax is so small it's negligible in my opinion. Thunking the exp_match didn't make a difference.
In some rare cases, I've seen Excel "take a peek ahead" in the evaluation process but that seems to only be when there's potentially a very large spill downstream. My theory is it needs to know how much memory to allocate.
Attached is the workbook I used for testing.
- m_tarlerSilver Contributor
Without doing some testing I'm not sure but I do know that certain functions will calculate fully regardless. So I do NOT think this is a function of the LET but rather a function of the IFNA and I haven't looked into the IFNA but the I know that the IF statement will NOT calculate the unused case in 'normal' or basic conditions but WILL calculate fully when it is passed an array for the conditional. So in your case, I think it depends if that conditional is or is considered an array then I suspect it will fully calculate. Basically (at least in the case of the IF statement) it goes from a native operator to a function with parameters and excel will calculate those parameters before passing it to the function. That all said there is a bit of work done using LAMBDA functions as THUNKS to get excel to pass those functions as unresolved functions and then only calculate the result at the end and showing significant performance improvements doing it that way. I'm not saying it will solve your issue but may make it more efficient if you are in need of performance improvements.