When it comes to working with Excel, mastering the various built-in functions can save you time, increase your efficiency, and unlock new ways to analyze your data. One such function, often underutilized but incredibly powerful, is the CHOOSE function, which is available in Excel 2016 and newer versions. In this guide, I’ll explain what the CHOOSE function does, how to use it, and provide you with practical, real-world examples to help you become an Excel power user.
What is the CHOOSE Function in Excel?
The CHOOSE function in Excel allows you to select a value from a list of options based on a given index number. It’s like a simplified version of a lookup function: you specify a number, and Excel returns the corresponding value from a set of choices you provide.
Why Use the CHOOSE Function?
- Quickly pick from a list of options without complicated formulas.
- Useful for creating drop-down dependent formulas, simple scenarios, or mock data.
- Combines well with other functions to create flexible, dynamic formulas.
CHOOSE Function Syntax
The syntax of the CHOOSE function is as follows:
=CHOOSE(index_num, value1, [value2], …)
- index_num: The position number in the list of values you want to return. This must be a number between 1 and 254.
- value1, value2, …: The possible choices (up to 254 in total).
Example:
=CHOOSE(2, “Red”, “Blue”, “Green”)
Result: Blue
Explanation: The index number is 2, so Excel picks the second value, which is “Blue”.
Basic Examples of the CHOOSE Function
Let’s start with some simple use cases to help you understand how CHOOSE works.
Example 1: Picking a Day of the Week
Suppose you have a number between 1 and 7 and want to return the corresponding weekday.
If cell A1 contains 1, the formula returns “Sun”
Example 2: Creating a Simple Menu
You want to return a meal based on a number.
Practical Uses for CHOOSE in Excel
Now that you’ve seen the basics, let’s explore how the CHOOSE function can help you solve real-world Excel problems.
1. Simulating a VLOOKUP with CHOOSE
Although VLOOKUP is often used to look up values, you can use CHOOSE to create a two-way lookup, especially for dynamic scenarios.
Example: Dynamic Lookup Table
Suppose you have sales data for two years in columns:
You want to pick which year’s sales to show based on a cell (say, cell D1: 1 for 2024, 2 for 2025).
Formula in cell D2:
=CHOOSE($D$1, B2, C2)
- If D1 = 1, you see sales for 2024.
- If D1 = 2, you see sales for 2025.
2. Generating Random Items from a List
Combine CHOOSE with the RANDBETWEEN function to randomly pick an item.
=CHOOSE(RANDBETWEEN(1,3), “Apple”, “Banana”, “Cherry”)
Each time you recalculate, Excel picks one fruit at random.
3. Creating Conditional Labels
Suppose you have a grading system:
- MATCH returns a number 1-5, which CHOOSE uses to assign “F”, “D”, “C”, “B”, or “A”.
4. Working with Dates
Pick the correct month, quarter, or label based on a number.
Combining CHOOSE with Other Functions
The true power of CHOOSE comes when you combine it with other functions for advanced scenarios.
Example: Dynamic Chart Series
You have two data series and want to switch which series your chart displays using a dropdown (Data Validation).
- Set up your options in a cell (1 = Sales, 2 = Profits).
- Use CHOOSE to dynamically reference your data:
=CHOOSE($B$1, SalesRange, ProfitsRange)
Where SalesRange and ProfitsRange are named ranges.
Example: Returning Ranges
You can return entire ranges with CHOOSE (useful in array formulas or dynamic charts).
=SUM(CHOOSE(2, B2:B10, C2:C10, D2:D10))
Returns the sum of column C.
Common Errors and Troubleshooting
1. #VALUE! Error
- Reason: The index number (index_num) is less than 1 or greater than the number of choices.
- Solution: Check that your index number is within the valid range.
2. #REF! Error
- Reason: You are trying to reference a range outside of what is available (for example, referencing a column that doesn’t exist).
- Solution: Double-check your value references.
3. Non-integer Index
- Reason: Index number is not an integer.
- Solution: Ensure the index is a whole number.
CHOOSE vs. INDEX vs. SWITCH: Which to Use?
- CHOOSE: Best for a small, fixed list of options, or when returning ranges or arrays.
- INDEX: Better for large lists, dynamic lookups, or when referencing ranges based on a row/column number.
- SWITCH: Great for exact matching of values.
Example:
- Use CHOOSE when you want =CHOOSE(2, “Apple”, “Banana”, “Cherry”) for simple selection.
- Use INDEX when you have a table and want =INDEX(A2:A100, 5).
- Use SWITCH for matching exact values: =SWITCH(A1, “A”, 1, “B”, 2, “C”, 3, “Other”).
Frequently Asked Questions
Q: Can CHOOSE be used with arrays?
A: Yes, you can use CHOOSE in array formulas, especially to select between ranges for dynamic charts or calculations.
Q: What is the maximum number of choices in CHOOSE?
A: Up to 254 values can be specified.
Q: Can CHOOSE be used with text, numbers, ranges, and formulas?
A: Yes, you can use CHOOSE to return text, numbers, cell references, ranges, or even calculations.
The CHOOSE function in Excel is a versatile, easy-to-use tool that can simplify many tasks, from random selections to dynamic reporting. While it may not replace more advanced lookup functions in every situation, it shines when you need to pick from a small set of options or return specific ranges dynamically.
By mastering CHOOSE, you add another valuable skill to your Excel toolkit, helping you work smarter and faster.
If you liked this post on How to Use the CHOOSE Function in Excel: A Comprehensive Guide, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.