Prevent Spill Array

[sparky2205]

Hi folks,
I have the following setup:

Pretty simple. DOW is a named range E2:E5.
I want to return a single value to B1, not a SPILL ARRAY.
I've tried using variations of the formula inserting @ at each of the obvious points but I either get the SPILL ARRAY or #VALUE error.

 

[Joe4]

Use this formula instead:

=INDEX(DOW,H1)

 

[sparky2205]

Hi Joe4,
I came across that when looking for solutions and it works fine.
I was just wondering if there is a way to prevent that spill array when using CHOOSE.

 

[Joe4]

It is because that is not how the CHOOSE function works. CHOOSE is not a replacement for INDEX, it is something entirely different.
You are telling it which value to return within the multiple VALUE arguments of the CHOOSE function.
The "Index_Num" value of 1 is telling it to return the value(s) found in the first Value argument, which is your entire DOW range.

For what you wanted to do, you would need to split up the range into multiple Value arguments, like this:

=CHOOSE(H1,E2,E3,E4,E5)

To read more on the CHOOSE function, see: CHOOSE function - Microsoft Support

 

[sparky2205]

Thanks for the explanation Joe4. Every day is a learning day.
Have a good day.

 

[Joe4]

No problem!
I must admit that I have not used the CHOOSE function much myself, so I had to look it up.
So it was a learning experience for me as well!

 

[Cubist]

Here's an alternative without using the list in col E.

=TEXT(H1 + 1, "dddd")

 

[Joe4]

Here's an alternative without using the list in col E.

=TEXT(H1 + 1, "dddd")

It should probably be mentioned that this only works with days of the week (in case they just posted a simple example, and may have other data that they are working with).
But it does work nicely for the example posted!

 

[sparky2205]

Thanks again folks.
That was just a simple example I posted to explain my predicament.
Nevertheless, good to know that TEXT option.

 

[Joe4]

Thanks again folks.
That was just a simple example I posted to explain my predicament.
Nevertheless, good to know that TEXT option.

Note it just works with the days in this case.
It makes clever use with how days are stored, and the various custom formatting options for dates.