CODEWITHSUNDEEP
excelexcel-formula
Dominique
Dominique

Reputation: 17533

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

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.

Upvotes: 3

Views: 106

Answers (1)

Ike
Ike

Reputation: 13044

=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.

Upvotes: 4

Related Questions