Let’s talk about the Excel CHOOSE function. We will cover its syntax, how it works, and ways to make the most of it. As always, I’ve also made a video that shows exactly what you need to know.
Table of Contents
Syntax of The Excel CHOOSE Function
It’s always important to understand the syntax of functions in Microsoft Excel. This shows how they work and what you need to include inside them.
=CHOOSE(index_number, value_1, value_2, etc.)
The CHOOSE function in Excel requires an index number and a list of values. That’s it. The index number shows which value to pull from the list of values. The list of values starts with value_1 and continues through all the values you list after it.
A note on index numbers: Unlike Python lists, which bgin with a 0 value, the index numbers in Excel begin at 1.
How to Use The CHOOSE Function in Excel
Here’s a simple example of how to use the CHOOSE function.
Say you have a list of items.
- Banana
- Apple
- Steak
- Pasta
- Blackberries
- Pears
If you want to pull a specific item from the list, the choose function makes it easy. The function to pull banana would look like:
=CHOOSE(1, "Banana", "Apple", "Steak", "Pasta", "Blackberries", "Pears")
If you want the result to be steak instead, the formula would look like this:
=CHOOSE(3, "Banana", "Apple", "Steak", "Pasta", "Blackberries", "Pears")
That’s because apples are in the first position of the list and cherries are in the third position.
Related: How to Randomize a List in Excel
Using a Reference Cell to Build Dashboards with CHOOSE
You can add a reference cell as the index number inside the CHOOSE function.
In this case, you might want the cell D1 to be the reference. To build on the example above, your formula would look like this:
=CHOOSE(D1, “Banana”, “Apple”, “Steak”, “Pasta”, “Blackberries”, “Pears”)
In that case, the result would change based on what you enter in cell D1. Enter 4 and the result will show “Limes”. Enter 6 and the result would appear as “Pears”.
Referencing an Existing List
You can’t replace value_1, value_2, etc with an existing list from your workbook. At least, you can’t do it directly. You must enter individual values there. However, you can work around that limitation by using another function like INDEX. You can also use array formulas as a workaround.
How to Use INDEX in Place of CHOOSE
You can use the INDEX function to reference a range, which allows you to select an item from a list without typing each value separately. Here’s an example. Let’s say you have the listed of items described in my earlier example in cells A1:A6. Â You can use INDEX instead of CHOOSE like this:
=INDEX(A1:A6, D1)
In this example, you still use the D1 reference cell to make your choice. Depending on your choice, it would pull from the index of existing options.
Best Ways to Use CHOOSE in Excel
Because you need to list values in the CHOOSE function, you might wonder when it’s most helpful. Short answer: It makes the most sense to use this function when:
- Working with a small, fixed value set
- Handling mixed types of values
- Working with conditional logic
- When values aren’t in a single range
Let’s dive into each of these examples. We will skip the small, fixed value set as that’s already described in the examples above.
Handling Mixed Value Types with CHOOSE
The Excel CHOOSE Function works really well when your options include letters, numbers, and symbols in any combination. For example, say you want to select between a number, a text string, and a reference cell.
=CHOOSE(D1, 485, "Text String", C52)
Here, you enter your index number as a value in cell D1 and the result shows either a numerical value (like 485) or a string of text or the contents of cell C52.
Working with Conditional Logic
Choose also excels with simple conditional logic. For example, it’s a great alternative to IF and SWITCH functions when you’re working with a small number of conditions. Sometimes you may want to change your result based on what someone selects in a drop-down menu.
In that case, you might use a formula like this:
=CHOOSE(D1, "Option 1: Basic Plan", "Option 2: Standard Plan", "Option 3: Premium Plan")
Here, if A1 contains the value 1, the formula returns “Option 1: Basic Plan”; if it contains 2, it returns “Option 2: Standard Plan”; and if it contains 3, it returns “Option 3: Premium Plan.” This makes CHOOSE a simple and effective alternative for managing small sets of conditional outputs without the complexity of nested IF statements.
Here’s how that same formula would look using IF statements:
=IF(D1=1, "Option 1: Basic Plan", IF(D1=2, "Option 2: Standard Plan", IF(D1=3, "Option 3: Premium Plan", "")))
Note that the CHOOSE function is the simpler choice here.
More Advanced Sequences
The CHOOSE Function also helps you create a sequence of results.
Consider this formula:
=CHOOSE(D1, SUM(A1:A5), AVERAGE(A1:A5), MAX(A1:A5))
If D1 is 2, it calculates the average of A1:A5. If your entry in D2 is 3, it would find the max value of the range. Neat!
Specific Questions and Answers
If you have more questions about your specific spreadsheet, please feel free to ask below!
[point_app]
Conclusion
In conclusion, the Excel CHOOSE function is a versatile tool that simplifies working with small, fixed sets of values, mixed data types, and simple conditional logic. Whether you’re selecting items from a list, managing dynamic dashboards, or creating custom sequences of operations, CHOOSE offers an easy-to-understand alternative to more complex functions like IF or SWITCH.
While it has some limitations—such as not directly referencing ranges—these can be easily overcome with functions like INDEX. Ultimately, CHOOSE shines when you need a straightforward, efficient way to handle predefined options in your Excel formulas.
