Home > OS >  How to take the n-th element of a "collection"-resulting formula in Excel?
How to take the n-th element of a "collection"-resulting formula in Excel?

Time:01-19

Recently, some new functions are added to Excel, resulting in a collection of cells, like Sequence() or Filter().

Does anybody know how to take an element of such a result?

E.g. I would like to know the fifth element of an arithmetic progression, starting with 8 and having a step of 12. I can simply do:

=SEQUENCE(5,1, 8, 12)

... and then go to the last cell of that sequence, but I would like to do something like:

=SEQUENCE(5,1, 8, 12) [5]

... but this, obviously, does not work.

CodePudding user response:

=INDEX(SEQUENCE(5,1, 8, 12), 5) will return the element of the 5th row and first column.

If there is only one column, like in your sample-sequence - it is the 5th element.

If you have more columns, you would have to decide what to put in the column part of the Index-formula. As an example, this will first create a 5x5 matrix with the arithmetic progression, starting at 8 with step 12, and then take the second row, third column:

=INDEX(SEQUENCE(5,5,8,12),2,3)

Result : 92.

  • Related