CHOOSE() me, an introduction to Excel CHOOSE function

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:

• Learn 5 IF formula tricks
• Using CHOOSE formula to calculate thanksgiving date
• Using CHOOSE formula to create dynamic charts