Generate in Excel 365 a matrix from rows and columns with one array function

Question

Suppose we want to make in Excel a matrix from a row of values and a column of values (or labels). For years a way to write a single formula to fill this matrix was to write an absolute column reference and an absolute row reference (e.g. =$A1+B$1 ) and fill the matrix. For example, like this.

For static matrices this works perfectly. Now with Excel 365 we have a range of array formulas that can create dynamic sequences and arrays. Suppose the matrix is defined by two dynamic sequences for rows and for columns as below, and its dimensions can change. Copy and paste a formula like before means that every time dimensions change, the formula will have to be copied and pasted manually to new location to fill the new matrix. Here is an example of such a dynamic matrix.

Is there a way to reproduce the original method of filling the matrix with sum of row and column values with one formula that uses array formulas(s) so that the formula will fill the whole matrix automatically, if either row dimension or column dimension changes?

I assume this formula will most likely not need for row and column inputs be laid out in cell, they can be fed to it as array inputs - but if not, it will still be a solution I seek.

I tried =MAKEARRAY(row, col, lambda(r, c, f(r,c)) ) but cannot figure out how I can pass arrays to lambda to iterate over.

Answer 1

Ok, after a few days of trying I found a solution. Not a very pretty one but working.

For math operators + - * / ^ () spill (#) reference does the trick:

=A2#*B1#

Some canned formulas can also take spill (#) inputs and generate an array:

=PMT(A2#/100,B1#,1000,0)

Some cannot. E.g. =MAX(A2#,B1#) will only return the single largest value.

However, it is still possible to produce a workaround.

=MAKEARRAY(COUNT(A2#),COUNT(B1#),LAMBDA(row,col,MAX(INDEX(A2#,row),INDEX(B1#,1,col))))

Step by step:

1. =COUNT(A2#) =COUNT(B1#) return length of row and column dynamic ranges
2. =MAKEARRAY(COUNT(A2#),COUNT(B1#),LAMBDA()) generates a rectangular dynamic array of these dimensions
3. LAMBDA(row,col,MAX(row,col)))) takes row index and column index as first two required inputs and passes them to MAX() formula
4. Finally, INDEX(A2#,row),INDEX(B1#,1,col) again take row and column array and return the values that match the indices, which MAX() processes.

E.g row=1 is converted to 1 and column=1 becomes 10. MAX(1,10) returns 10 to LAMBDA() and MAKEARRAY(1,1,LAMBDA()) populates the array at position 1,1 with 10. Repeat until all pairs of row=A2# and column=B1# are done.

Hope this helps others who may be looking for a solution to this problem.