I generate a matrix with "MAKEARRAY" and I would like to be able to recover a submatrix of this matrix. I was thinking of using "OFFSET" but apparently it only works on range or cell.
I tried this :
=LET(data;MAKEARRAY(2;2;LAMBDA(row;col;row+col));OFFSET(data;;;1;1))
But i get "#value" as a result (this is rather normal because we cannot use OFFSET with a matrix) Offset Documentation
Another solution using the INDEX
function:
Semi-colon convention:
=LET(
array; A1:B4; row_offset; 1; col_offset; 1;
IFERROR(
INDEX(
array;
row_offset + SEQUENCE(ROWS(array));
col_offset + SEQUENCE(1; COLUMNS(array))
);""
)
)
Comma convention:
=LET(
array, A1:B4, row_offset, 1, col_offset, 1,
IFERROR(
INDEX(
array,
row_offset + SEQUENCE(ROWS(array)),
col_offset + SEQUENCE(1, COLUMNS(array))
),""
)
)