Using a named range ARRAY inside an OFFSET function

Hi all,

I need to reference an array inside an offset function, where the array spills from B8:P8. I currently reference this by using B8#:

Please Login or Register  to view this content.

The problem is that I want to replace the B8# with a namedrange that contains the formula contained in B8:

Please Login or Register  to view this content.

Is there a way to create a named range and replace the B8#? This is designed to expand and contract depending on how many different yearly changes in impact - hence the need for the arrays.

I have attached a workbook with my data as an example.

Originally Posted by Bo_Ry

This requires Office Insider with Lamba Function

=B2*SCAN(1,actualimpactnamedrange,LAMBDA(z,x,z*x))

Unfortunately, I do not have office insider. Is this the only solution?