The gist for this lambda function can be found here.
I was straining my brain to understand the lambdas posted by user XLambda on the lambda forum at mrexcel.com.
This person is really mining the depths of what’s possible with lambda in Excel. I go there frequently when I want to understand how to do something.
While reading about ASCAN, I found myself watching this video by Leila Gharani which showed how to calculate yeartodate sums (YTD) using the SCAN function.
This got me thinking about a common calculation I’ve seen used in my work over the years
Calculate the % growth that this month represents over the same month of the prior year
If we have a value for March 2022, then the calculation is:
=[value from March 2022]/[value from March 2021]1
This is easy enough. It’s also common enough to warrant a named lambda.
This is GROWTHFROMOFFSET:
=LAMBDA(month_col,value_col,month_offset,[if_error],
IFERROR(
LET(
current_row,ROW()MIN(ROW(month_col))+1,
this_month,INDEX(month_col,current_row),
this_value,INDEX(value_col,current_row),
compare_month,EDATE(this_month,1*month_offset),
compare_value,SUMIF(month_col,compare_month,value_col),
this_value/compare_value1
),
IF(ISOMITTED(if_error),NA(),if_error)
)
)
It works like this:
This lambda does not spill.
Because of this, it can be used in a table as shown above, or it can be used against a range.
If using against a range, you must be sure to use absolute cell references in the first two parameters ($A$2:$A$37 instead of A1:A37).
GROWTHFROMOFFSET takes three required parameters:
 month_col – this is the array or Table column or range of data (using absolute cell references) that holds the month for the current row
 value_col – this is the array or Table column or range of data (using absolute cell references) that holds the value which you want to calculate growth for
 month_offset – the number of months before the current month you want to calculate growth from. In the example above, I have calculated growth using 12 months before the current month
There is also an optional parameter
 [if_error] – this is a value to show if the formula returns an error. This is usually where there is no data available for the calculation. For example, if the current row is Jan2022 and the first available data is for Apr2021, then 12 months before Jan2022 is Jan2021 and the formula cannot produce the calculation. In this case, we can pass a value to [if_error] to show instead of #N/A. If this parameter is not provided, the row will show #N/A
The Lambda is simple, but let’s break it down:
name  definition 

The row where the formula is entered: ROW(), minus the minimum row from the list of rows for month_col: MIN(ROW(month_col)) If month_col is on rows 5:10, then ROW(month_col)={5;6;7;8;9;10} and MIN(ROW(month_col))=5. If the formula is on row 7, then 75=2. This number represents the index position in the month_col, as we see below. 

Here we use INDEX to get the month from the current row. 

Similarly, we use INDEX to get the value from the current row. 

We use the EDATE function to return the date that is month_offset months before this_month 

We use the SUMIF function to sum value_col where month_col is equal to compare_month 
Finally, we perform the simple calculation this_value/compare_value1 to calculate the output.
If that value returns an error, then we check if a value for [if_error] was provided. If it wasn’t, we return #N/A, otherwise we return [if_error].
And that’s that.
A practical lambda that will make sure each time we need to calculate this “growth” metric by months, it will always work the same way. There’ll be no mistakes by putting the numerator and denominator the wrong way round, or forgetting the 1, or accidentally selecting 11 months before the current month.
This is just one implementation of this function to calculate % growth of current month over 12 months before.
Hopefully it will inspire you to name the calculations you use regularly to reduce the possibility of error.
Share generously!