Why does non-recursive LAMBDA/LET slow things down?

[adamdom]

Hi all! Hopeful that you can help me speed up some of my formulas. I'm building a chain of LAMBDA functions and the most recent one is running really slowly and I can't figure out why because if I run the exact same formula just filling in the variables, it runs instantly. Details below:

Runs slowly (2+ minutes): LAMBDA(x,BYROW(x,LAMBDA(r,IFERROR(MATCH(TRUE,ARR_Row_Detail(x,ROW(r)-1)<>0,0),-1)))) <- passing in ARR_Grid_Full_Detail for x
Runs slowly (2+ minutes): LET(x,ARR_Grid_Full_Detail,BYROW(x,LAMBDA(r,IFERROR(MATCH(TRUE,ARR_Row_Detail(x,ROW(r)-1)<>0,0),-1))))
Runs instantly: LET(x,ARR_Grid_Full_Detail,BYROW(x,LAMBDA(r,IFERROR(MATCH(TRUE,ARR_Row_Detail(ARR_Grid_Full_Detail,ROW(r)-1)<>0,0),-1))))

For context this is running on ~9000 rows so I understand why calc times could build up in general but I dont understand why doing the function one way runs instantly (< 2 seconds) and when I just pass in the variable to a lambda it takes forever.

Other definitions in name manager in case helpful (all run instantly and do exactly what I want):
ARR_Row_Detail: =LAMBDA(grid,row,OFFSET(grid,row-1,0,1,num_dates))
num_dates: 31
ARR_Grid_Full_Detail: =OFFSET('Raw Data'!$A$1,1,MATCH(MIN('Raw Data'!$1:$1),'Raw Data'!$1:$1,0)-1,COUNT('Raw Data'!$G:$G)-1,MATCH(MAX('Raw Data'!$1:$1),'Raw Data'!$1:$1,0)-MATCH(MIN('Raw Data'!$1:$1),'Raw Data'!$1:$1,0)+1) <- basically just returns a grid (~9000 x 31) that I am looking for.

Happy to answer questions and thanks in advance!

 

[cmowla]

I don't know if it's the only cause, but you are using OFFSET. That, among other functions like INDIRECT will slow down performance significantly. See Volatile Excel Functions -Decision Models.