Using Choose With Sequence (or nested Choose)

[RicoS]

Hi,

I'm experimenting with some stuff in Excel (because I'm a geek, mainly) and I came across a problem without solution. I expect it doesn't have a solution, but that's fine.

It's specific to those with access to dynamic array formulas only (probably, I think...). I can write the word "Test" in a cell, and in the adjacent cell type =TRANSPOSE(SEQUENCE(3)), which gives me the following:

Test

1

2

3

What I can't do is get the same returned when using =CHOOSE({1,2},"Test",TRANSPOSE(SEQUENCE(3)))

It seems that the CHOOSE formula cannot be nested (or used in combination with sequence or other array) to return more columns than the initial choose statement's proposal. Even changing the formula to CHOOSE({1,2,3,4,5}... has no bearing. Interestingly, the number returned in the first column is the second value in the array too, so if I subtract 1 then the first column value is the correct value, but subsequent columns are not filled out. If I change the formula to:

=CHOOSE({1,2,3,4},"Test",(TRANSPOSE(SEQUENCE(4,1,0,1))))

I get the correct number of columns returned, but the last two columns get the #VALUE! error.

Anyway, I'm guessing there is no way to "nest" the CHOOSE function in this manner. If anyone finds a solution, that'd be cool. If not, it's really just something I was pondering anyway!

 

[J.Ty.]

Just thinking aloud:

Change the formula to
=CHOOSE({1,2,3,4},"Test",(TRANSPOSE(SEQUENCE(4,1,0,1))),"A")
and you will notice that TRANSPOSE(SEQUENCE(4,1,0,1)) does not produce something like elements seprated by commas to be appended as arguments of CHOOSE, but an obejct(?) which Excel attempts to put in one cell. However, it does not result in a #SPILL error which you typically get when an array function has not enough room to display its result completely.

J.Ty.

 

[J.Ty.]

However, nested CHOOSE spills all the way it needs to present its full content, and even more.

Book1
	G	H
8
9	A	A
10	B	B
11	C	C
12	1	2
Sheet1

Cell Formulas
Range	Formula
G9:H12	G9	=CHOOSE({1;2;3;4},"A","B","C",CHOOSE({1,2},1,2))
Dynamic array formulas.

and SEQUENCE can be the first argument of CHOOSE:

Book1
	J	K	L
3	A	B	C
Sheet1

Cell Formulas
Range	Formula
J3:L3	J3	=CHOOSE(SEQUENCE(1,3,1,1),"A","B","C")
Dynamic array formulas.

 

[RicoS]

Just thinking aloud:

Change the formula to
=CHOOSE({1,2,3,4},"Test",(TRANSPOSE(SEQUENCE(4,1,0,1))),"A")
and you will notice that TRANSPOSE(SEQUENCE(4,1,0,1)) does not produce something like elements seprated by commas to be appended as arguments of CHOOSE, but an obejct(?) which Excel attempts to put in one cell. However, it does not result in a #SPILL error which you typically get when an array function has not enough room to display its result completely.

J.Ty.

That's not what I get actually, I get a return of: Test,1,A,#VALUE! in the four columns, where the 1 represents the first return value of the sequence, and the #VALUE! appears to represent Excel not recognising the SEQUENCE extending beyond the first returned value. Thus, if I change the {1,2,3,4} to {1,2,3}, I get an #N/A which suggests that Excel thinks that the sequence is "breaking out" of the original CHOOSE range (original choose suggests 3 columns, sequence suggests more). It's strange, but understandable.

Based on your other comments, I should clarify that I'm simply attempting to return a 1D range rather than a 2D, which is what a nested choose appears to return.

It's an intriguing one. I'm guessing it's beyond the scope of the dynamic array (dynamic array within a dynamic array sort of thing). Probably for the best, I'd just end up breaking things if it worked!

 

[J.Ty.]

What about this:

Book1
	P	Q	R	S	T
4	2	7	12	17	22
5	A	A	A	A	A
6	4	9	14	19	24
Sheet1

Cell Formulas
Range	Formula
P4:T6	P4	=CHOOSE(SEQUENCE(3,1,1,1),SEQUENCE(1,5,2,5),"A",SEQUENCE(1,5,4,5))
Dynamic array formulas.

 

[J.Ty.]

I think the typing system of Excel prevents things you want from happening. An array is a single value of TYPE 64, while CHOOSE expects a tuple of types.

 

[J.Ty.]

Am I right, that what you are looking for is indeed a method to append a symbol to an array, so that it behaves like a list?

 

[J.Ty.]

I have figured out how to concatenate two arrays:

Book1
	O	P	Q	R
18	1	2	100	101
Sheet1

Cell Formulas
Range	Formula
O18:R18	O18	=IF(SEQUENCE(1,4,1,1)<3,INDEX(SEQUENCE(1,2,1,1),SEQUENCE(1,4,1,1)),INDEX(SEQUENCE(1,2,100,1),SEQUENCE(1,4,1,1)-2))
Dynamic array formulas.

 

[RicoS]

It's a nice formula, but no, I wasn't looking to append a symbol to an array or anything like that! I put in a simple example to avoid having to explain, but of course you've come back with some great suggestions thinking I want something else! I apologise for time-wasting!

I was actually trying to use CHOOSE() to return an entire table of values. The first column ("Test") is actually a SPILLED list, and then second, third etc are matching that list back to the table to get the values for a particular column. To illustrate:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Test No	Entry1	Entry2	Entry3	Entry4		Test No	Entry1	Entry2		Test No	Entry1	Entry2
2	Test1	304	222	386	381		Test10	518	202		Test10	202	#VALUE!
3	Test2	606	551	747	846		Test11	140	301		Test11	301	#VALUE!
4	Test3	337	155	315	779		Test12	359	959		Test12	959	#VALUE!
5	Test4	902	192	786	154		Test13	246	572		Test13	572	#VALUE!
6	Test5	826	572	983	625		Test14	593	713		Test14	713	#VALUE!
7	Test6	749	466	227	203		Test15	943	321		Test15	321	#VALUE!
8	Test7	488	860	417	780		Test16	561	817		Test16	817	#VALUE!
9	Test8	386	718	466	835		Test17	955	432		Test17	432	#VALUE!
10	Test9	370	851	388	660		Test18	157	401		Test18	401	#VALUE!
11	Test10	518	202	759	308		Test19	544	287		Test19	287	#VALUE!
12	Test11	140	301	168	952
13	Test12	359	959	429	377
14	Test13	246	572	769	650
15	Test14	593	713	934	366
16	Test15	943	321	746	663
17	Test16	561	817	979	851
18	Test17	955	432	278	173
19	Test18	157	401	882	670
20	Test19	544	287	890	377
Sheet2

This formula returns exactly as I wish, with a index(match()) returning the values for Entry1 and Entry2.

Book2
	G
2	Test10
Sheet2

Cell Formulas
Range	Formula
G2:I11	G2	=CHOOSE({1,2,3},FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),INDEX($A$1:$E$20,MATCH(FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),$A$2:$A$20,0)+1,MATCH("Entry1",$B$1:$E$1,0)+1),INDEX($A$1:$E$20,MATCH(FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),$A$2:$A$20,0)+1,MATCH("Entry2",$B$1:$E$1,0)+1))
Dynamic array formulas.

I was trying to slim down the formula (and potentially expand for X number of Entry columns, represented by the sequence value:

Book2
	K
2	Test10
Sheet2

Cell Formulas
Range	Formula
K2:M11	K2	=CHOOSE({1,2,3},FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),INDEX($A$1:$E$20,MATCH(FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),$A$2:$A$20,0)+1,MATCH("Entry"&TRANSPOSE(SEQUENCE(2)),$B$1:$E$1,0)+1))
Dynamic array formulas.

As you can see in the first table, the second Entry column isn't returned using SEQUENCE. And, indeed, the SEQUENCE seems to return "Entry2" data first for some reason (unless I change to SEQUENCE(2,1,0,1)), but either way the second column doesn't return anyway.

Again, this is mainly a thought experiment for my own intrigue, so don't waste time on it unless you're interested in an Excel voyeuristic manner like myself!

 

[Fluff]

You can get that result like

+Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Test No	Entry1	Entry2	Entry3	Entry4		Test No	Entry1	Entry2		Test No	Entry1	Entry2
2	Test1	304	222	386	381		Test10	518	202		Test10	518	202
3	Test2	606	551	747	846		Test11	140	301		Test11	140	301
4	Test3	337	155	315	779		Test12	359	959		Test12	359	959
5	Test4	902	192	786	154		Test13	246	572		Test13	246	572
6	Test5	826	572	983	625		Test14	593	713		Test14	593	713
7	Test6	749	466	227	203		Test15	943	321		Test15	943	321
8	Test7	488	860	417	780		Test16	561	817		Test16	561	817
9	Test8	386	718	466	835		Test17	955	432		Test17	955	432
10	Test9	370	851	388	660		Test18	157	401		Test18	157	401
11	Test10	518	202	759	308		Test19	544	287		Test19	544	287
12	Test11	140	301	168	952
13	Test12	359	959	429	377
14	Test13	246	572	769	650
15	Test14	593	713	934	366
16	Test15	943	321	746	663
17	Test16	561	817	979	851
18	Test17	955	432	278	173
19	Test18	157	401	882	670
20	Test19	544	287	890	377
Sheet2

Cell Formulas
Range	Formula
G2:I11	G2	=CHOOSE({1,2,3},FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),INDEX($A$1:$E$20,MATCH(FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),$A$2:$A$20,0)+1,MATCH("Entry1",$B$1:$E$1,0)+1),INDEX($A$1:$E$20,MATCH(FILTER($A$2:$A$20,LEN($A$2:$A$20)=6),$A$2:$A$20,0)+1,MATCH("Entry2",$B$1:$E$1,0)+1))
K2:M11	K2	=FILTER(FILTER(A2:E20,(LEN(A2:A20)=6)),(A1:E1=K1)+(A1:E1=L1)+(A1:E1=M1))
Dynamic array formulas.