Best way to get the value of the last (or any) element of an array?

[felixstraube]

I was working on a formula and I needed to get the value of the last item of an array to create a sequence from 1 to that value. Something like this:

Book1
	A	B
1	Array	No the result i wanted
2	5	1
3	10
4	15
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SEQUENCE(CHOOSEROWS(A2:A4, -1))

but instead of getting an array of 15 numbers i got the "1" as you see in the example, which let me scratching my head at first.
It took me some time to realize that CHOOSEROWS actually returns an array of only one item, ({15}), instead of the value itself (15). And feeding that into SEQUENCE didn't work.
Then I used INDEX as a workaround like this:

Book1
	A	B	C
1	Array	No the result i wanted	INDEX workaround
2	5	1	1
3	10		2
4	15		3
5			4
6			5
7			6
8			7
9			8
10			9
11			10
12			11
13			12
14			13
15			14
16			15
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SEQUENCE(CHOOSEROWS(A2:A4, -1))
C2:C16	C2	=SEQUENCE(INDEX(CHOOSEROWS(A2:A4,-1), 1))
Dynamic array formulas.

This worked and I continued with my formula.
Now the question is: What is the best way, or best practice, to get the actual value from one item of an array? Is there a better way than using INDEX?

 

[Scott Huish]

=SEQUENCE(LOOKUP(9.9999999999E+307,A:A))

 

[felixstraube]

Sorry, I didn't write it clearly.
The array could be the result of a FILTER function or any other that returns a dynamic array.

 

[dreid1011]

You may be missing some parameters for SEQUENCE.. perhaps one of these:

Edit: Nvm about the missing parameters part. I see SEQUENCE will assume starting value of 1, and defaults to 1 column.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Array	Result
2	5	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
3	10	1
4	15	2
5		3
6		4
7		5
8		6
9		7
10		8
11		9
12		10
13		11
14		12
15		13
16		14
17		15
Sheet1

Cell Formulas
Range	Formula
B2:P2	B2	=SEQUENCE(1,TAKE(A2:A4,-1),1)
B3:B17	B3	=SEQUENCE(TAKE(A2:A4,-1),1,1)
Dynamic array formulas.

 

[felixstraube]

TAKE returns the value, not an array of only one item, and feeding it to SEQUENCE it works fine, i get my sequence.
What if I want the penultimate item?
Which of this options do you think is the best? Is there another, better, cleaner way?

Book1
	A	B	C	D
1	Array	No the result i wanted	with INDEX	with TAKE
2	5	1	1	1
3	10		2	2
4	15		3	3
5	20		4	4
6			5	5
7			6	6
8			7	7
9			8	8
10			9	9
11			10	10
12			11	11
13			12	12
14			13	13
15			14	14
16			15	15
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SEQUENCE(CHOOSEROWS(A2:A5, -2))
C2:C16	C2	=SEQUENCE(INDEX(CHOOSEROWS(A2:A5,-2), 1))
D2:D16	D2	=SEQUENCE(TAKE(TAKE(A2:A5, -2),1))
Dynamic array formulas.

 

[RoryA]

I'd use INDEX:

=SEQUENCE(INDEX(A2:A5,COUNT(A2:A5)-1), 1)

 

[dreid1011]

Agreed with Rory, TAKE only looks at the first or last elements in an array. If the value you seek is in a different position, TAKE won't get you there.

 

[felixstraube]

Thanks all for the answers! They where all helpful!