**How To Use**

The CHOOSE Function In Excel

The CHOOSE Function In Excel

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

The ‘CHOOSE’ function is super useful and should be second-nature to you.

We’re going to explore how to apply the function with some practical examples.

You’re also going to learn how to combine ‘CHOOSE’ with a couple of advanced features and methods. This demonstrates the real power of this simple little function.

So, buckle up and let’s get right into it!

**The basics of ‘CHOOSE’**

The concept of the ‘CHOOSE’ function is quite straightforward.

**‘CHOOSE’ takes some values and chooses one of those based on an index number we supply in the first argument.**

That said, let’s take a closer look at the syntax…

**Syntax of ‘CHOOSE’**

**‘index_num’ –**This is a numerical value like ‘1’ or ‘2’ that the function requires.**‘value1’ –**This is a required argument and it can be a string, number, cell reference, or a function.**‘[value2]…’ –**This and all following arguments are optional. The same concept for**‘value1’**(above) applies.

So, if we have a function with three values, our ‘index_num’ arguments should be any number 1 to 3.

An ‘index_num’ value of ‘1’ would return the item in the ‘value1’ argument. An ‘index_num’ of ‘2’ would return the item in the ‘value2’ argument.

See how that works?

Let’s look at an example to solidify our understanding a bit more.Let’s start by creating a formula using the ‘CHOOSE’ function.

This helps us select a string text month name by selecting the proper numerical value of the month.

For example, if we select ‘6’ as our ‘index_num’, we will expect the ‘CHOOSE’ function to select ‘June’ as its output.

**So here is what our full formula should look like:**

If we supply ‘1’ as the ‘index_num’, the formula will output ‘January’ since it is the first value in the list.

Similarly, if we select ‘9’, our formula will give us ‘September’ since it is the ninth value argument.

Very straightforward 🙂

We can also use cell references in our ‘CHOOSE’ formula.So, let’s create a list of month numbers and their equivalent names like we have here in cells ‘A3:B14’.

Then we can use these cell references in place of the literal values we used in our previous formula!

Notice in the picture below that we can use a mix of cell references and literal values – or all cell references to achieve the same goal.

But we can leverage this concept even further with a bit of extra Excel savvy.

**Let’s create a simple table that allows us to input the number for our ‘index_num’ with the result in the cell next to it.**

If we input a number in the ‘Select Number’ field, the resulting month name will appear in the ‘Month’ field.

Typing in the value manually in the ‘Select Number’ field is easy.

But what if we would like to control the range of values a user can input?Let’ say we want to limit the user to the range 1-12. Here’s how you do that…

We can do this by using a tool called ‘Data Validation’.

First, we select the value field, cell ‘D15’ in our example.

Then we click on the ‘Data’ tab on the ribbon. Then find ‘Data Validation’ in the ‘Data Tools’ section.

The ‘Data Validation’ dialog box opens.

Select ‘List’ from the ‘Allow:’ dropdown on the ‘Settings’ tab.

Once ‘List’ is selected, **make sure the ‘Ignore blank’ check box is selected.**

Also, make sure that the ‘In-cell dropdown’ checkbox is checked.

The next step is to place our range of numbers 1 through 12 in the ‘Source:’ input field.

**To do this, start by clicking into the field.**

Then click and drag our cursor down from ‘A3’ to ‘A14’ to select the entire list of numbers 1 through 12.

Once we verify that this is indeed the range of cells, in the ‘Source:’ field click ‘OK’.

**Now we have a drop-down list in cell ‘D15’.**

This drop-down includes the numerical values we need rather than relying on keyed inputs.

Our next step is to place our ‘CHOOSE’ formula into cell ‘E15’ and our table is complete.

Now we can change our ‘Select Number’ value from the dropdown – and the ‘Month’ value will change accordingly.

Like we did in the last formula (where we used cell references for all our arguments) we will do the same thing here.

Instead of having to change the cell reference for our ‘index_num’ every time we want to select a different month, simply set it to ‘D15’.

**What this does is: **

Every time a number is selected from the drop-down list, the ‘CHOOSE’ function in our formula will respond appropriately.

Now you have seen a bit of what ‘CHOOSE’ is capable of and how useful it is.

Let’s look at another interesting scenario where the function again proves very useful!

**Combining the ‘CHOOSE’ function with other Excel tricks**

Consider a case in which we have a list of test grades for 25 students.

**Our task is to develop a worksheet to calculate several metrics on demand. **

We also need to make a count of those grades within each letter grade range.

The ‘CHOOSE’ function will come in very handy here. But we will also use a couple of other Excel tricks to impress all our friends at the same time. Sounds like a win-win, right?

**First, let’s establish our metrics:**

We want to calculate the **mean** (average), **median**, **maximum**, and **minimum** for our grade result range.

Also, we want to calculate the number of test grades that fall within each range **A**, **B**, **C**, **D**, and **F**.

We will use ‘CHOOSE’ to do __both__ tasks.

**The ‘CHOOSE’ function syntax requires the ‘index_num’ for the first argument.**

This parameter tells the function which of the subsequent value arguments to choose.

This can be a literal numeric value – or a cell reference containing a numerical value *(like our previous examples)*.

We can use this in a fancy way by using a combo box to select our metrics and grade ranges. This makes Excel convert those to a number value.

The values we place in our combo box like ‘mean’, ‘median’, ‘max’, and ‘min’ will be set to numeric values 1, 2, 3, and 4, respectively.

This is what the ‘CHOOSE’ function will recognize as the ‘index_num’ argument.

**Let us now create our metrics:**

**Steps to creating a Combo Box**

Now, back to creating our fancy combo box.

To do this, we will need to go to the ‘Developer’ tab.

**No ‘Developer’ tab? Here’s how to get it**

If the ‘Developer’ tab is not shown on the ribbon, go to the ‘File’ tab and click on ‘Options’. This opens the ‘Excel Options’ dialog box.

Find and click on ‘Customize Ribbon’. Find the checkbox next to ‘Developer’ in the right column and make sure it is checked.

**Click ‘OK’ and the ‘Developer’ tab now shows!**

**1****:** Once on the ‘Developer’ tab, find the ‘Controls’ section and click on ‘Insert’.

**2:** Under ‘Form Controls’ on the drop down that appears, hover over the second icon from the left until ‘Combo Box (Form Control)’ appears.

If something else appears, hover over each icon until that text appears and click on it.

**3:** Then move the cursor down in the worksheet until a plus sign appears as the cursor.

**4:** Click and drag to the size of a rectangle similar to the size of a cell.

**5:** Right click on the resulting combo box. Then select ‘Format control…’ from the drop down that appears.

Then the ‘Format Control’ dialog box should appear:

**a)** Click into the ‘Input range’ input box and then highlight the cell range containing our ‘Metrics’ (‘H3:H6’).

**b)** Then click in the ‘Cell link’ input box and select cell ‘E3’. This is the cell that shows the numerical value equivalent to the selections in the list of our ‘input range’.

**c)** Once we click ‘OK’ we can see that as we change our selections in the combo box dropdown, the number in cell ‘E3’ changes.

**d)** Now right click on the combo box to enter edit mode and grab and drag over cell ‘E3’.

**Protip:** hold down the ‘Alt’ key while you do this. Excel then attempts to align the combo box with the upper left corner of the cell. Then grab the corners and sides to resize as necessary to fit the cell.

Now that we have setup our first combo box, we can repeat the process for our grade ranges.

**Our final setup should look something like this:**

In our ‘Result’ field for the ‘Select Metric’ table, we now place the following formula:

‘=CHOOSE(E3,AVERAGE(C3:C27),MEDIAN(C3:C27),MAX(C3:C27),MIN(C3:C27))’

Notice first, that we have set ‘E3’ as our ‘index_num’ argument.

This is the number value in the cell that changes as we make a different selection for metric in the combo box drop-down list. Simply right click on the combo box to put it in edit mode. Then grab and drag out of the cell to view the numerical values change in the cell itself.

**Next, notice how we have placed formulas for the necessary metrics in the value arguments of our ‘CHOOSE’ formula.** Note that they are in the order that our list of metrics is in.

Our ‘CHOOSE’ formula for the grade ‘Range’ table also uses formulas as the value arguments.

While the entire formula appears somewhat complicated, it is rather repetitive.

We simply use the ‘COUNTIF’ function that analyzes the same range of grades but changing the ‘criteria’ argument for each.

Each different ‘criteria’ defines a grade score range according to its appropriate letter grade.

‘=CHOOSE(E6,COUNTIF($C$3:$C$27,”>89″),

COUNTIFS($C$3:$C$27,”>79″,$C$3:$C$27,”<90″),

COUNTIFS($C$3:$C$27,”>69″,$C$3:$C$27,”<80″),

COUNTIFS($C$3:$C$27,”>59″,$C$3:$C$27,70″),

COUNTIFS($C$3:$C$27, “> 49”,$C$3:$C$27, “< 60”))’

**Using ‘CHOOSE’ in a macro**

One of the great things about Excel is its ability to automate processes with macros.

Functions can also be used in the VBA code of macros and ‘CHOOSE’ is no exception. Let’s see how to use ‘CHOOSE’ in a macro!

**In this example, we have set up a macro to run when a button is clicked.**

*This* will then trigger a dialog box that asks for the user to input a number between 1 and 3.

Each number is assigned to a color. The number 1 is red, the number 2 is green and the number 3 is blue.

When the user types in one of those numbers, the VBA code in the macro is written so that this input will be the value in cell ‘A1’.

The code also has cell B1 populate with the appropriate color that we have assigned to each number.

It populates with the actual text string of the color name and it also fills the cell with that color.

We use the ‘CHOOSE’ function in the VBA code itself to achieve this.

This article isn’t about how to write VBA code. It’s just a quick example showing you the possibilities of combining CHOOSE with VBA.

**Here we’ve made use of the ‘CHOOSE’ function to achieve the outcome of placing the color value, as well as the color itself, in cell ‘B2’.**

That said, let’s look at the code…

**The main difference in the ‘CHOOSE’ function of this VBA code is that it uses the ‘R1C1’ style.**

Under more simple circumstances, you’d be using literal cell references.

This means that we are using a cell reference style that is based on a single reference location on the worksheet.

‘**R**’ stands for __row__ – and as you may have guessed, ‘**C**’ stands for __column__.

A positive number indicates how many rows down or to the right from reference cell location. Conversely, a negative number for the row is the number of rows upward while for the column it is that many to the left.

Let’s say our reference cell location was ‘D10’, ‘R[-1]C[1]’.

This would be the equivalent of ‘E9’ (one row above…’R[-1]’… and one column to the right…’C[1]’… of ‘D10’).

**Another thing to note is that if we are using ‘R1C1’ style, a zero is not required for no change in row or column. **

That would just be RC.

Another example would be that the ‘R1C1’ notation for cell ‘D11’ in relation to ‘D10’ would be ‘R[1]C’. No number is required to the column reference since it would be 0.

The line in our VBA code, ActiveCell.FormulaR1C1 = _ “=CHOOSE(RC[-1],””Red””,””Green””,””Blue””)”, basically tells Excel to place the ‘CHOOSE’ formula between the double quotes into the active cell (which happens to be ‘B1’).

Knowing that ‘RC[-1]’ in relation to ‘B1’ (again, the active or selected cell) is ‘A1’, the ‘index_num’ of our formula is cell ‘A1’.

Therefore, our ‘index_num’ argument will change according to the user input and select the appropriate color value to place in ‘B1’.Then the rest of the code runs and changes the cell fill color accordingly.

This is an example of how we can use VBA to insert the ‘CHOOSE’ function (or any other function!) into the cell of our choosing.

The use of VBA provides the flexibility to have Excel do this based on other events – such as the selection of a number value from an input box.

**Conclusion**

Now we have looked at a vast spectrum of scenarios that we can apply the ‘CHOOSE’ function to.

This gives you a broad perspective on how we can use the function so solve various challenges.

Excel offers so many tools and functions that the same problem can often be solved from a variety of strategies.

This was a look at how the ‘CHOOSE’ function can help you overcome different challenges in an efficient (and easy!) way.