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:
| Col1 | Col2 | Col3 | Col4 | Col5 | |
| Row1 | Col1Row1 | Col2Row1 | Col3Row1 | Col4Row1 | Col5Row1 |
| Row2 | Col1Row2 | Col2Row2 | Col3Row2 | Col4Row2 | Col5Row2 |
| Row3 | Col1Row3 | Col2Row3 | Col3Row3 | Col4Row3 | Col5Row3 |
| Row4 | Col1Row4 | Col2Row4 | Col3Row4 | Col4Row4 | Col5Row4 |
| Row5 | Col1Row5 | Col2Row5 | Col3Row5 | Col4Row5 | Col5Row5 |
| Row6 | Col1Row6 | Col2Row6 | Col3Row6 | Col4Row6 | Col5Row6 |
I was thinking that this formula should work:
=MAP(C2:G2; B3:B8; LAMBDA(ColName; RowName; ColName&RowName))
It spills an array in the correct size, but produces "Value not Available" errors, except for the first cell.
| Col1 | Col2 | Col3 | Col4 | Col5 | |
| Row1 | Col1Row1 | #N/A | #N/A | #N/A | #N/A |
| Row2 | #N/A | #N/A | #N/A | #N/A | #N/A |
| Row3 | #N/A | #N/A | #N/A | #N/A | #N/A |
| Row4 | #N/A | #N/A | #N/A | #N/A | #N/A |
| Row5 | #N/A | #N/A | #N/A | #N/A | #N/A |
| Row6 | #N/A | #N/A | #N/A | #N/A | #N/A |
Any ideas?
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
