Help with CHOOSE function

[sooshil]

CHOOSE function has syntax

[COLOR=#2F2F2F][FONT=&quot]CHOOSE(index_num, value1, [value2], ...)

I have 100s of values (text values) to deliver to CHOOSE function. Instead of typing all those values every time, I want to keep those values as defined name. I tried to define a name and handed overt to CHOOSE, but it understands that name as the value1.
How can I overcome this situation?
What I did?
Defined a name:
MyName = "FirstValue","Second","Third","Fourth"

=ChOOSE(1,MyName)[/FONT][/COLOR][COLOR=#2F2F2F][FONT=&quot]

[/FONT][/COLOR][FONT=&quot]gives [/FONT][FONT=&quot]"FirstValue","Second","Third","Fourth"
for all other index numbers it gives #VALUE error.
[/FONT]
How can this problem be solved?
Thanks

 

[DanteAmor]

Try this other approach.

=INDEX(myname,3)

 

[sooshil]

Try this other approach.

=INDEX(myname,3)

I tried that approach too. That didn't work.
If I hardcode the array inside Index, it works, but when I put the Name, It gives #REF error.
Any more thought on it?

 

[FormR]

but when I put the Name, It gives #REF error.

Hi, how exactly did you define your named range?

If you want the text values hard coded in the named range try setting "Refers to:" to, for example:
={"FirstValue","Second","Third","Fourth"}

Or alternatively put your 100's of text values in separate cells in a column and have your named range refer to that range of cells.

Then try the INDEX() function again..

 

[sooshil]

Thank you so much for both of you for your effort.
I really appreciate it.

 

[DanteAmor]

I had not understood that this was a macro. The function I gave was to put it on the excel sheet as a formula.

I'm glad you found the solution.
Thanks for the feedback.

 

[sooshil]

I had not understood that this was a macro. The function I gave was to put it on the excel sheet as a formula.

I'm glad you found the solution.
Thanks for the feedback.

No.
I was talking about Excel Formula not a macro or VBA.
��

 

[DanteAmor]

Then the formula works.

You must use it as follows.

 

[sooshil]

DanteAmor

Sorry for the misunderstanding. Yes the way FormR suggested works fine.
At first I was looking to give those values to CHOOSE as a named range.
Then you suggested to use INDEX, I didn't realize that I need to use separate column to store values for named range.
That I knew from FormR.
So, thank you both.

 

[DanteAmor]

Youre welcome!