Tips for Excel, Word, PowerPoint and Other Applications

The CHOOSE Function in Excel

Why It Matters To You

CHOOSE is a nifty little function that lets you specify a value or an action to perform based on an index number. Formulas can be hardcoded values or links to cells. Actions can be named formulas or formulas you write into the CHOOSE function. It's very useful for driving user input for scenerios and other types of models.

How To ...

CHOOSE is simple. For the most part, we'll just work through the Function Wizard. Here is a sample file to help you follow along. If you've read the Named Formulas article, then you'll recognize this example.

=CHOOSE(C9,add,subtract,multiply)

There are only two types of arguments required for the CHOOSE function. You need an index number (Index_num), which must be a whole number 1 or greater, and then a number of values (e.g., Value1, Value2, etc up to a maximum of 39). CHOOSE will return the value or execute the action of Value[n] based on the index_num. So, if your index number is 2, then Excel does whatever is specified as Value2. If for some reason, your index number exceeds the number of values you've specified, Excel will retun a VALUE#. One way to make sure this doesn't happen is to control the index number with a Combo Box or data validation.

A lot of times, it will make more sense to your users if they know what each CHOOSE option is. Choosing between 1 or 2 isn't nearly as meaningful as choosing between "Aggressive Scenario" and "Conservative Scenario". Remember however, that the index number has to be a whole number, not text. So how do we have to change our data validation options to drive the CHOOSE formula? One answer is to add a n- in front of each option and then strip it out for the CHOOSE function.

To set up the data validation, go to your input cell and open the data validation wizard (ALT+D, L). Choose list and specify the following in the source of values: 1-Add,2-Subtract,3-Multiply, which should look like this:

If you're familiar with the various Forms Tools, you can use these too as they directly output numerical values based on your choice.

Now, we'll modify the formula above a little bit to extract our index number. We're going to use a LEFT function to extract the left most character from the data validation cell.

=CHOOSE(LEFT(C18,1),add,subtract,multiply)

Now our user has a list that indicates what they are choosing and the choices are limited to 1-3 to ensure my formula won't create errors, which may mess up dependant calculations.

Notes

Last updated	9/1/08
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	None
Related Tips	Data Validation Text Parsing in Excel: LEFT, MID, RIGHT Combo Boxes