Choose function is not giving result for Static Array input

[Junarkar]

Hi

I have written below formula;

INDEX(CHOOSE(WEEKDAY(TODAY()),WeekNumber),WEEKDAY(C$10))

WeekNumber is a static array. It contains below info.

{"WeekDay1","WeekDay2","WeekDay3","WeekDay4","WeekDay5","WeekDay6","WeekDay7"}

WeekDay1,2,3 etc. are also static arrays. All of them contains numbers.

I want choose function to return the array name according to index number provided by Weekday().

Sample file is attached.

 

[Chihiro]

Formula syntax requires each value/array to be separated by comma.
i.e. =CHOOSE(index_Num,Item1,Item2,Item3...)

You can't use it like below...
=CHOOSE(2,{"X","Y"})

This will result in error as 2nd item to pick isn't present. While 1 in index_num argument will work as Item1 = {"X","Y"}.

 

[Chihiro]

Oh, by the way, if you want to use WeekNumber, use INDEX() instead of CHOOSE().

EX:
=INDEX(INDEX(WeekNumber,WEEKDAY(TODAY())),WEEKDAY(C$10))

 

[Junarkar]

Thanks Chihiro for the fast response...!!! But need little more help please.

Now I have changed formula as below;
($G21*INDEX(INDEX(WeekNumber,WEEKDAY($N$1)),WEEKDAY(C$10)))/100

But still returns #VALUE! error. When evaluated, what I understood is that;

* First second Index function - INDEX(WeekNumber,WEEKDAY($N$1), returns the correct array name. WeekDay5 in this scenario.
* Second Index function is not recoganising this as an Array instead consider it as a text string.

Please help.

 

[Chihiro]

You'll probably have to nest the inner INDEX in INDIRECT. Since name as string can't directly be used to reference named range.

=($G21*INDEX(INDIRECT(INDEX(WeekNumber,WEEKDAY($N$1))),WEEKDAY(C$10)))/100

 

[Junarkar]

Hi Chihiro

Thanks. I just tried, but it returns #REF! error.

 

[Chihiro]

Hmm, you may need to build your named ranges using actual ranges.
I just realized hard coded names can't be referenced using INDIRECT.

Ex: Add sheet "Names"

In A1:E1, enter WeekDay1 ~ WeekDay5
Then in rows below values for each name.

Name A2:A8 as WeekDay1, B2:B8 as WeekDay2 etc.

Now you can refer to it using Indirect.

 

[Haz]

Alternatively, add a new name WeekEval that refers to this formula
=EVALUATE(INDEX(WeekNumber,WEEKDAY(Week!$N$1)))

Then in C11
=($G21*INDEX(WeekEval,WEEKDAY(C$10)))/100

 

[Chihiro]

Ah good idea! Forgot that you could use Evaluate in named range.

 

[Junarkar]

Alternatively, add a new name WeekEval that refers to this formula
=EVALUATE(INDEX(WeekNumber,WEEKDAY(Week!$N$1)))

Then in C11
=($G21*INDEX(WeekEval,WEEKDAY(C$10)))/100

Wow...!! That worked like a charm... I put it in a single line though.
=($G21*INDEX(EVAL(INDEX(WeekNumber,WEEKDAY($N$1))),WEEKDAY(B$10)))/100

Thanks a lot Haz....