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

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!

This is exactly my own motivation, too. So no worries.

 

[Fluff]

Based on this

The first column ("Test") is actually a SPILLED list

you could also use

+Fluff.xlsm
	A	B	C	D	E					J	K	L	M
1	Test No	Entry1	Entry2	Entry3	Entry4						Test No	Entry1	Entry2
2	Test1	304	222	386	381						Test10	518	202
3	Test2	606	551	747	846						Test11	140	301
4	Test3	337	155	315	779						Test12	359	959
5	Test4	902	192	786	154						Test13	246	572
6	Test5	826	572	983	625						Test14	593	713
7	Test6	749	466	227	203						Test15	943	321
8	Test7	488	860	417	780						Test16	561	817
9	Test8	386	718	466	835						Test17	955	432
10	Test9	370	851	388	660						Test18	157	401
11	Test10	518	202	759	308						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
A2:A20	A2	="Test"&SEQUENCE(19)
K2:M11	K2	=FILTER(FILTER(A2#:E2,(LEN(A2#)=6)),(A1:E1=K1)+(A1:E1=L1)+(A1:E1=M1))
Dynamic array formulas.

 

[RicoS]

Yes, Fluff, yes you could indeed. These are nice formulas, and something I'll definitely use going forward. However, again, in my attempt to not get hung up on the data and create a simple example to work from, I accidentally left open a good opportunity for you to provide me with a way round my problem! I was really just interested in the CHOOSE() formula in a nested situation.

If I can make it harder for you, this might help! Imagine the first column of our generated table (K1:M1) is generated by formula rather than filter (it could be anything, but imagine that the results are always in the table). For example, using your: ="Test"&SEQUENCE(19) formula as the first part of your second formula:

Book2.xlsx
	G
14	#VALUE!
Sheet2

Cell Formulas
Range	Formula
G14	G14	=FILTER(FILTER("Test"&SEQUENCE(19),(LEN("Test"&SEQUENCE(19))=6)),(A1:E1=K1)+(A1:E1=L1)+(A1:E1=M1))

Thus, the second filter in this [above] example will cause the formula to error as it doesn't know what it's filtering. We'd have to replace all the arguments with the Test&SEQUENCE. Essentially the "Test"&SEQUENCE(19) generates the first column of my generated table (K1:M1), I then want to use the CHOOSE function for columns 2 and 3 to MATCH that generated list back to the table of data. Like this:

Book2.xlsx
	G
2	Test10
Sheet2

Cell Formulas
Range	Formula
G2:I10	G2	=CHOOSE({1,2,3},"Test"&SEQUENCE(9,1,10,1),INDEX($A$1:$E$20,MATCH("Test"&SEQUENCE(9,1,10,1),$A$2:$A$20,0)+1,MATCH("Entry1",$B$1:$E$1,0)+1),INDEX($A$1:$E$20,MATCH("Test"&SEQUENCE(9,1,10,1),$A$2:$A$20,0)+1,MATCH("Entry2",$B$1:$E$1,0)+1))
Dynamic array formulas.

I want to merge the last two INDEX(MATCHES()) by using "Entry"&SEQUENCE(2) instead of Entry1 and Entry2.

In this hypothetical example.....!

 

[Fluff]

It doesn't appear that you can do that & if you can I don't know how.

 

[RicoS]

Well, thanks for trying! I did enjoy the nested FILTER formula, so it was worth it (for me)!

 

[Fluff]