Using Choose With Sequence (or nested Choose)

RicoS

New Member
Joined
May 1, 2019
Messages
18
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:

Test123

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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,138
Office Version
  1. 365
Platform
  1. Windows
Based on this
The first column ("Test") is actually a SPILLED list
you could also use

+Fluff.xlsm
ABCDEJKLM
1Test NoEntry1Entry2Entry3Entry4Test NoEntry1Entry2
2Test1304222386381Test10518202
3Test2606551747846Test11140301
4Test3337155315779Test12359959
5Test4902192786154Test13246572
6Test5826572983625Test14593713
7Test6749466227203Test15943321
8Test7488860417780Test16561817
9Test8386718466835Test17955432
10Test9370851388660Test18157401
11Test10518202759308Test19544287
12Test11140301168952
13Test12359959429377
14Test13246572769650
15Test14593713934366
16Test15943321746663
17Test16561817979851
18Test17955432278173
19Test18157401882670
20Test19544287890377
Sheet2
Cell Formulas
RangeFormula
A2:A20A2="Test"&SEQUENCE(19)
K2:M11K2=FILTER(FILTER(A2#:E2,(LEN(A2#)=6)),(A1:E1=K1)+(A1:E1=L1)+(A1:E1=M1))
Dynamic array formulas.
 

RicoS

New Member
Joined
May 1, 2019
Messages
18
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
RangeFormula
G14G14=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
2Test10
Sheet2
Cell Formulas
RangeFormula
G2:I10G2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,138
Office Version
  1. 365
Platform
  1. Windows
It doesn't appear that you can do that & if you can I don't know how.
 

RicoS

New Member
Joined
May 1, 2019
Messages
18
Well, thanks for trying! I did enjoy the nested FILTER formula, so it was worth it (for me)!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,064
Messages
5,545,784
Members
410,706
Latest member
antstt
Top