Today lets learn about Excel CHOOSE() function.
CHOOSE eh? What does it do?
To understand CHOOSE() and appreciate its uses, lets invent an imaginary boss-subordinate pair.
Jasmine is the boss. She is, well, lets call her peculiar. She likes olives, Tuesdays & color Red. She hates potatoes.
Martin is the faithful butler of Jasmine. He is obedient, quirky and tall. He likes lotuses, Fridays & color blue. He hates potassium.
Enter Jasmine’s scarf problem:
Jasmine likes to wear a different colored scarf every weekday. She likes to wear Red colored scarf on Mondays & Tuesdays. She likes to put on the blue polka dot scarf on Wednesdays. On Thursdays, she wears her olive colored scarf. On Fridays & Saturdays, she prefers the lovely orange blue scarf. Sundays are no scarf days.
No wonder she is peculiar.
On the first day of his job, Martin understood this schedule. Although he did raise his eyebrows (in bewilderment) more than once, he knew a butler should never question.
So everyday, soon after waking up, Martin would open up the list of scarf requirements, and figure out the scarf for that day. He would then neatly lay it out on Jasmine’s bed while she is in the shower.
Soon this all got boring.
So Martin thought, “Wouldn’t it be cool if I can feed the scarf schedule to a computer so it automatically told me which scarf to choose everyday morning!.”
Martin reached out to his ninja computer friend who knew how to do this.
YOU.
Your Excel solution for Jasmine’s scarf problem
After hearing the entire story, raising eyebrows a few times and looking at Martin with eyes full of pity, you set out to create an Excel workbook that told him which scarf to pick on any given date.
It is simple & elegant.
In the Cell B3, you wrote =TODAY()
so that everyday A3 will tell what is the latest date.
In B4, you wrote =WEEKDAY(B3)
Then in B5, you wrote a lengthy nested IF formula to figure out the scarf of the day.
Scarf of the day formula:
=IF(B4=1,"No scarf",IF(B4<=3,"Red scarf",IF(B4=4,"Blue polka dot scarf", IF(B4=5,"Olive colored scarf","Orange blue scarf"))))
Martin couldn’t be happier. Now that he has an awesome Excel file telling him what scarf to pick everyday, he has one less thing to worry.
BUT….
Soon after your Excel file, Jasmine had to replace the polka dot scarf with yellow striped one (she slipped an olive on the scarf while eating and it left a permanent mark).
While at it, she also changed the schedule.
And now, Martin is back to square one.
Late that week, he explained the problem to you over a drink. You quickly modified the file to suit new scarf of the day scenario.
The formula now looked like this:
=IF(B4=1,"No scarf",IF(OR(B4=2,B4=4),"Red scarf",IF(B4=3,"Yellow striped scarf", IF(B4=5,"Olive colored scarf","Orange blue scarf"))))
Thats when you got thinking.
The nested IF formula is awfully long and clumsy to maintain. May be there is a better one?!?
Enter CHOOSE formula, built for scarf of the day & more
CHOOSE() formula works beautifully for situations like this.
Instead of the long & clumsy nested IF formula, you could simply write a choose formula.
Syntax of the CHOOSE formula:
The CHOOSE formula is simple to write.
=CHOOSE(some number, value 1, value 2, value 3....)
and CHOOSE will pick a value based on some number.
For example,
=CHOOSE(3,"Chandoo.org","makes","you","awesome")
will result in you.
Scarf of the day CHOOSE Formula:
For our scarf of the day, the choose formula looks like this:
=CHOOSE(B4,"No scarf","Red scarf","Yellow striped scarf", "Red scarf","Olive scarf","Orange blue scarf","Orange blue scarf")
(or this if you want it for schedule prior to olive accident, =CHOOSE(B4,"No scarf","Red scarf","Red scarf","Blue polka dot scarf", "Olive scarf","Orange blue scarf","Orange blue scarf") )
Okay, what else can CHOOSE() do?
CHOOSE cant make you tall, rich or beautiful yet. But, it can do few more things.
Here is one such powerful example.
Fetch one range from many using CHOOSE
We can use CHOOSE() to fetch one of the many ranges, like this:
=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))
This will result in the sum of the 2nd range, ie B1:B10.
Here is an interesting example of this:
How many values can CHOOSE take?
CHOOSE() can take up to 254 different values and return one of them based on the index number (first parameter).
What if I have more than 254?
Forget 254. Anytime you want to choose one value from more than a few (say 10), CHOOSE formula becomes tedious (as you have to select individual value cells or type them).
For all such cases (ie when you have list of values more than 10), I suggest using INDEX(). It is a powerful & versatile formula designed to handle situations like this.
Example workbook:
Here is the example workbook on CHOOSE. When you click the link, it opens Excel inside browser so you can practice this anywhere.
Do you CHOOSE?
I write CHOOSE() formulas often. It is a simple formula and I find several uses for it.
What about you? Do you use CHOOSE()? What are some of your favorite uses of it? Please share your thoughts using comments.
More examples on CHOOSE
Check out these additional examples to learn more:
24 Responses to “CHOOSE() me, an introduction to Excel CHOOSE function”
One of the more esoteric uses of the Choose() function is to allow the Vlookup() function to lookup data to the left of the Lookup column...
Yes, Despite everyone telling you that VLookup only looks to the Right, It can actually lookup to the left!
The process is described here:
http://chandoo.org/wp/2012/09/06/formula-forensics-no-028/
Hrm, while this is a clever way to show how CHOOSE works (and thanks for reminding me it works for ranges! I always forget that), it's still not an ideal solution to the problem, because you still have to count positions to figure out which day to change. I think it would be better to have a table and use INDEX to pick the right scarf. That way a quick glance will show you the whole week's plan, and you don't risk ruining your formula if you have to change one of the scarves.
and Choose() is ideal for unpivoting a table of data, say a table of accounting entries with a column for every month. Using Choose() twice (with a lookup table of numbers 1 thru 12) you unpivot for each month, each value of each account.
Jeff, that sounds interesting. Could you provide more details as to how that would work? Thanks.
Please forgive me for not quite visualizing this one. Got an example? Thanks!
Me too, I want to know how to do this!
I love 'CHOOSE' and often use it to apply various aggregations
or split ranges - saves me a lot of time when developing or analysing on the fly.
I use one cell for the entry choice - in the examples below, I would enter 1,2 or 3 into cell D20 to use the formulas.
Various calculations:
=CHOOSE(B20,AVERAGE(B2:B13),MAX(C2:C13),STDEV.P(E2:E13))
Split Range:
=SUM(CHOOSE(B20,B2,C2,D2):F13)
My mistake. I would use cell B20 and not D20 for the formulas to work.
Assemble an array on the fly. Have used it to get around certain formula requirements for having adjacent cells as an input (i.e. LOGEST). Example:
CHOOSE({1,2,3,4,5},A1,D1,G1,J1,M1)
Using in this format for the "known y's" input to LOGEST and LINEST, we can effectively eliminate the need to have the values adjacent to one another.
Can anyone give me a real world example of why you would use Choose over index/match? I understand how to use it, but why would you when index/match works?
On first glance it seems to me that it is always easier to use INDEX(MATCH()) and your formula ends up being more concise and easier to read. It is easier for me to direct a user to update a table than to have them update the details of the CHOOSE function.
I guess the benefit is that the "table" you would make INDEX/MATCH look at is imbeded in the CHOOSE function?? So as long as it is only an excel user working in the file it saves creating the table some where else?
Here is my favorit use of CHOOSE. Figuring out non-calendar year quarter numbers.
http://datapigtechnologies.com/blog/index.php/choosing-quarters-in-excel/
HA ha ha ha, Mike, that's great, wonderfully simple and earned great praise form Mr Peltier himself.
I hadn't realised it until this article but 'CHOOSE' is probably one of the cutest formulas in Excel. Sure, there are better formulas, like INDEX/MATCH, and more powerful ways of gaining results with VBA, but CHOOSE is really a fun formula and makes me smile each time I use it in the same way I smile at my dogs for doing something silly.
Should a formula really be FUN... or do I need to get out more?
leonk:
Remember the group you are speaking to here. We all think there is FUN in functions. Bwahahahaha.
Amazing function! I am currently using it for Days of a week/ Months of a year and avoiding unnecessary columns and functions. Thanks Chandoo
I confess that while I used to use =CHOOSE years ago, I can think of no good use of it any more, and now consider it bad programming form in nearly every circumstance.
Much better to use =LOOKUP, =VLOOKUP, =MATCH, or =INDEX(MATCH()).
And for converting months to quarters: =LOOKUP(month, {1,4,7,10},{1,2,3,4}).
One nice use of CHOOSE is as a pre-2007 substitute for IFERROR, particularly if the formula in question is of a considerable length so as to make repeating it within an IF(ISERROR(... clause a little unwieldy, i.e.:
=LOOKUP(REPT("z",255),CHOOSE({1,2},"",[someformula]))
where the result of [someformula] here is assumed to be a text value (the lookup_value can be changed accordingly for a range consisting of numericals or a mix of text/numericals).
If the result of [someformula] is an error, e.g. #N/A, the lookup_vector part will become:
CHOOSE({1,2},"",#N/A)
which, since the LOOKUP function performs the required array coercion, becomes:
{"",#N/A}
and the LOOKUP then returns "" from this array, as required. Clearly, if the result of [someformula] is NOT an error, then LOOKUP will return this value from the array in preference to "".
Regards
Hi there-
Great example Chandoo! I've downloaded the workbook, and attempted to "recreate it" from scratch. Everything seems to be functioning fine, but, I cannot seem to figure out how to get the conditional formatting to work? I have it identical to your set up, but, ALL of my columns highlight when I pick "Chocolates", then nothing highlights when I pick another option.
Any idea?
Thanks!
I figured it out. I anchored ($) the C in the C$5=$I$5.
Now I am awesome!
Hello
in example 1, to find the sum of any products data,I tried to use match function without putting table name but it was not working. i put it like =MATCH(I5,$B$5:$F$17,0)
hi,
Another thing which i noticed is if we put only the choose function without sum the result shows the second value of lst.products. why like that? why it always selects the second value
Hello,
I try to use vlookup with choose function,but some is wrong ... below I paste the table
start grade role saving
2013 a an 174.000,00 lei
2014 d vp 224.000,00 lei
2013 c vp 204.000,00 lei
2013 b avp 278.000,00 lei
2013 d dir 370.000,00 lei
2014 a an 163.000,00 lei
2014 b avp 270.000,00 lei
2013 a an 122.000,00 lei
2014 d dir 221.000,00 lei
criteria
2014 #REF!
d
dir
and the formula wich I use:
=VLOOKUP(A13&A14&A15;CHOOSE({1,2};A2:A10&B2:B10&C2:C10;D2:D10);2;0)
where is the problem ?
Tks
I would like to ask about how you do the lst.product in the J5 cell.
Thank you
I use CHOOSE as an alternative for generating quarters from a date as explained here https://www.youtube.com/watch?v=L4iXlhxO-d0
You could also use it in a dashboard scenario where you wish to display top or bottom 5 items. If a user selects "TOP 5" then Excel switches to LARGE formula, if they choose "BOTTOM 5" then the SMALL function comes into play:
CHOOSE("X", LARGE(B5:B15, 2), SMALL(B5:B15, 2))
This link won't work. Its about choose practice workbook. Below link:
https://1drv.ms/1jwv8Uw