Help with CHOOSE function

sooshil

Board Regular
Joined
Feb 21, 2013
Messages
104
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
CHOOSE function has syntax
Code:
[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"

Code:
=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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this other approach.

=INDEX(myname,3)
 
Upvote 0
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?
 
Upvote 0
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..
 
Last edited:
Upvote 0
Thank you so much for both of you for your effort.
I really appreciate it.
 
Upvote 0
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.
 
Upvote 0
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.
��
 
Upvote 0
Then the formula works.

You must use it as follows.

ba904841cf4913e0cccf6297ce1943ed.jpg
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top