Forum Discussion
Solved
Spilling an array with given column and row cells
I'd like to create a formula that will use given column and row cells and spill an array. In this example the bold text would be given and the rest should automatically spill based on those: ...
It would be useful if you were to share an example of the type of calculation you're wishing to spill, as it might still be possible to do so without a LAMBDA-based function. Failing that, you could try MAKEARRAY, for example:
=LET( a,B3:B8, b,C2:G2, MAKEARRAY(ROWS(a),COLUMNS(b), LAMBDA(x,y,LET(p,INDEX(b,y),q,INDEX(a,x),p&q)) ) )Regards
Thanks Jos, for this simple example this would be it. In the actual problem I try to solve the column headers are some numbers, and the row labels some other numbers, and I'd like to do a complex calculation based on the combination of these two. I'd like to do this with a single array formula in the first cell, and not fill the whole grid with individual formulas.
It would be useful if you were to share an example of the type of calculation you're wishing to spill, as it might still be possible to do so without a LAMBDA-based function. Failing that, you could try MAKEARRAY, for example:
=LET( a,B3:B8, b,C2:G2, MAKEARRAY(ROWS(a),COLUMNS(b), LAMBDA(x,y,LET(p,INDEX(b,y),q,INDEX(a,x),p&q)) ) )Regards
- Wow, a thousand thanks! I tried with the formula you gave, and it works like a charm. It's incredible what one can do nowadays with Excel, but it sure takes getting used to with all these new functionalities. Thanks again!