CHOOSE function: First Argument is "index_num" - Can I reference an Array??

[jase71ds]

Hi kind folks.
As I've been learning the CHOOSE function, I've fallen in love. It's such a humble, but powerful, little function.

The arguments (according to intelli-sense) are:
CHOOSE(index_num, value1, [value2], [value3], [value4],...)

The index_num argument can be a cell reference, but apparently only if the cell in reference is a single value (not an array)
Is there a way around this??

For example:
This is a valid formula: =CHOOSE( {1,2}, A1:A2, B1:B2 )

But, if D1 is set to {1,2}, and the formula is =CHOOSE( D1, A1:A2, B1:B2 ), it breaks, because D1 gets evaluated as "{1,2}" with the quotes!

It would work if D1 would be evaluated, sans the strings (quotes).
Is there a way to evaluate D1 and strip the quotes (")???
When evaluated, I need D1 to be parsed as literally {1,2}, not as "{1,2}"

Hope that made sense.
Maybe a shorter version of the question: How to evaluate a cell reference that is an array, as an actual array and not a string?

 

[Fluff]

If D1 contains {1,2} then it is a string & not an array.
Why not put the two values into D1 & E1 & use

=CHOOSE( D1:E1, A1:A2, B1:B2 )

 

[Domenic]

Try the following . . .

1) First, define the following (Ribbon >> Formula tab >> Defined Names group >> Define Name) . . .

Name:  ChooseValue

Refers to:  =EVALUATE('Sheet1'!$D$1)

2) Then, use the following formula . . .

=CHOOSE(ChooseValue,A1:A2,B1:B2 )

Hope this helps!

 

[jase71ds]

Why not put the two values into D1 & E1 & use

=CHOOSE( D1:E1, A1:A2, B1:B2 )

Thanks. Seems like that is what I will have to do.
I was hoping that a single cell could hand off an array value to a function - but it's not looking like that's possible (apart from VBA).

 

[jase71ds]

Try the following . . .

1) First, define the following (Ribbon >> Formula tab >> Defined Names group >> Define Name) . . .

Name:  ChooseValue

Refers to:  =EVALUATE('Sheet1'!$D$1)

2) Then, use the following formula . . .

=CHOOSE(ChooseValue,A1:A2,B1:B2 )

Hope this helps!

Thanks, but I should have mentioned that I was hoping for a non-VBA method.

 

[Domenic]

In that case, how about simply storing your array in a define name, and then referring to that name in your formula? So, for example . . .

Name:  ChooseIndex

Refers to:  ={1,2}

Then your formula would be . . .

=CHOOSE(ChooseIndex,A1:A2,B1:B2)

 

[jase71ds]

In that case, how about simply storing your array in a define name, and then referring to that name in your formula? So, for example . . .

Name:  ChooseIndex

Refers to:  ={1,2}

Then your formula would be . . .

=CHOOSE(ChooseIndex,A1:A2,B1:B2)

Ohhh. That's an interesting approach!
Thanks, I'll look into it.

 

[Peter_SSs]

I was hoping that a single cell could hand off an array value to a function

Assuming the string in D1 is something you are creating then could you leave out the curly brackets and just have "1,2" there?

Then since you have MS 365 you either will have, or will 'sometime' have, the new TEXTSPLIT function and you could use the formula in F1 below. If you do not have the TEXTSPLIT function yet then you could use the formula in I1.

If D1 must include the curly brackets then see the corresponding options in the bottom section.

jase71ds.xlsm
	A	B	C	D	E	F	G	H	I	J
1	a	c		1,2		a	c		a	c
2	b	d				b	d		b	d
3
4
5				{1,2}		a	c		a	c
6						b	d		b	d
Sheet1

Cell Formulas
Range	Formula
F1:G2	F1	=CHOOSE(TEXTSPLIT(D1,","),A1:A2,B1:B2)
I1:J2	I1	=CHOOSE(TRANSPOSE(FILTERXML("<p><c>"&SUBSTITUTE(D1,",","</c><c>")&"</c></p>","//c")),A1:A2,B1:B2)
F5:G6	F5	=CHOOSE(TEXTSPLIT(MID(D5,2,LEN(D5)-2),","),A1:A2,B1:B2)
I5:J6	I5	=CHOOSE(TRANSPOSE(FILTERXML("<p><c>"&SUBSTITUTE(MID(D5,2,LEN(D5)-2),",","</c><c>")&"</c></p>","//c")),A1:A2,B1:B2)
Dynamic array formulas.

 

[jase71ds]

Peter,
I am on MS 365, but it appears that I don't yet have TEXTSPLIT. I know that my company is on the delayed roll-out, so I'm not sure when I'll get it.
The FILTERXML hurts my simple brain.
I've got a solution that is working. But I'll admit, I'd really like to be able to pass the values of a single cell, to a formula as an array.
But for now, it's working.
Thanks very much for your time. I'll re-visit this when I get the latest MS 365 Corporate update - probably won't be too much longer.
Jase.