excelexcel-formulaoffset

Excel, How to use "OFFSET" on the result of a "MAKEARRAY"


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


Solution

  • 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))
         ),""
       )
     )