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!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
However, nested CHOOSE spills all the way it needs to present its full content, and even more.
Book1
GH
8
9AA
10BB
11CC
1212
Sheet1
Cell Formulas
RangeFormula
G9:H12G9=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
JKL
3ABC
Sheet1
Cell Formulas
RangeFormula
J3:L3J3=CHOOSE(SEQUENCE(1,3,1,1),"A","B","C")
Dynamic array formulas.
 

RicoS

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

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web

ADVERTISEMENT

What about this:
Book1
PQRST
427121722
5AAAAA
649141924
Sheet1
Cell Formulas
RangeFormula
P4:T6P4=CHOOSE(SEQUENCE(3,1,1,1),SEQUENCE(1,5,2,5),"A",SEQUENCE(1,5,4,5))
Dynamic array formulas.
 

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

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
I have figured out how to concatenate two arrays:

Book1
OPQR
1812100101
Sheet1
Cell Formulas
RangeFormula
O18:R18O18=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

New Member
Joined
May 1, 2019
Messages
18
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
ABCDEFGHIJKLM
1Test NoEntry1Entry2Entry3Entry4Test NoEntry1Entry2Test NoEntry1Entry2
2Test1304222386381Test10518202Test10202#VALUE!
3Test2606551747846Test11140301Test11301#VALUE!
4Test3337155315779Test12359959Test12959#VALUE!
5Test4902192786154Test13246572Test13572#VALUE!
6Test5826572983625Test14593713Test14713#VALUE!
7Test6749466227203Test15943321Test15321#VALUE!
8Test7488860417780Test16561817Test16817#VALUE!
9Test8386718466835Test17955432Test17432#VALUE!
10Test9370851388660Test18157401Test18401#VALUE!
11Test10518202759308Test19544287Test19287#VALUE!
12Test11140301168952
13Test12359959429377
14Test13246572769650
15Test14593713934366
16Test15943321746663
17Test16561817979851
18Test17955432278173
19Test18157401882670
20Test19544287890377
Sheet2


This formula returns exactly as I wish, with a index(match()) returning the values for Entry1 and Entry2.
Book2
G
2Test10
Sheet2
Cell Formulas
RangeFormula
G2:I11G2=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
2Test10
Sheet2
Cell Formulas
RangeFormula
K2:M11K2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
You can get that result like
+Fluff.xlsm
ABCDEFGHIJKLM
1Test NoEntry1Entry2Entry3Entry4Test NoEntry1Entry2Test NoEntry1Entry2
2Test1304222386381Test10518202Test10518202
3Test2606551747846Test11140301Test11140301
4Test3337155315779Test12359959Test12359959
5Test4902192786154Test13246572Test13246572
6Test5826572983625Test14593713Test14593713
7Test6749466227203Test15943321Test15943321
8Test7488860417780Test16561817Test16561817
9Test8386718466835Test17955432Test17955432
10Test9370851388660Test18157401Test18157401
11Test10518202759308Test19544287Test19544287
12Test11140301168952
13Test12359959429377
14Test13246572769650
15Test14593713934366
16Test15943321746663
17Test16561817979851
18Test17955432278173
19Test18157401882670
20Test19544287890377
Sheet2
Cell Formulas
RangeFormula
G2:I11G2=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:M11K2=FILTER(FILTER(A2:E20,(LEN(A2:A20)=6)),(A1:E1=K1)+(A1:E1=L1)+(A1:E1=M1))
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,514
Messages
5,548,500
Members
410,841
Latest member
rcp
Top