Forum Discussion
Does a LET variable get computed even if it's only referenced inside IFNA's fallback argument?
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.