Creating a dynamic 2d array

Question

I have to create variable length sequences from a column along its rows.
Eg- Suppose the column is as below

2
4
3

I want to create a sequence like this

2 | 1 | 2
4 | 1 | 2 | 3 | 4
3 | 1 | 2 | 3

I'm able to create a single sequence using the SEQUENCE function but to spread it over the entire column, I'm not able to use the ARRAYFORMULA correctly.
If I write ARRAYFORMULA(SEQUENCE(A1:A3)), it gives me a column of three ones.

Answer 1

You can create a matrix like this:

=arrayformula( if( sequence(1, max(A1:A)) <= A1:A, sequence(1, max(A1:A)), iferror(1/0) ) )

The formula uses the open-ended reference A1:A, and will thus process all rows in column A. If you want to limit the formula to a predefined number of rows, use a bounded reference like A1:A42.

To limit the formula to the continuous rows where column A contains a number, use filter(), like this:

=arrayformula( if( sequence(1, max(A1:A)) <= filter(A1:A, isnumber(A1:A)), sequence(1, max(A1:A)), iferror(1/0) ) )

To limit the formula to the last row that contains a number, use the max(row() * isnumber()) pattern, like this:

=arrayformula( if( sequence(1, max(A1:A)) <= filter(A1:A, row(A1:A) <= max(row(A1:A) * isnumber(A1:A))), sequence(1, max(A1:A)), iferror(1/0) ) )

A suggested edit proposed to use indirect() for the same purpose. indirect() is a volatile function that gets recalculated every time any value in the spreadsheet is edited. It thus hurts spreadsheet performance and is best avoided.