The CHOOSE function in Excel allows you to return a value from a list of options based on a number you provide. Think of it like telling Excel to “pick option number 1, 2, 3, or 4” from a predefined list.
This function is especially useful when creating simple category systems, assigning labels, converting numbers to text values, or reducing long nested IF formulas. While CHOOSE is not one of Excel’s most commonly used functions, it can save time when you need Excel to select from a small group of fixed choices.
In This Guide, You’ll Learn
- What the CHOOSE function does
- How CHOOSE syntax works
- How to use CHOOSE with simple examples
- How to return text, numbers, and categories
- Common mistakes to avoid
- Real-world uses for the CHOOSE function
What is the CHOOSE Function?
The CHOOSE function returns a value from a list based on a position number (called an index number). For example, if you tell Excel to choose option 2 from a list of months, Excel will return the second month in the list.
You can use CHOOSE with numbers, text, formulas, and even cell references.
CHOOSE Function Syntax
=CHOOSE(index_num, value1, [value2], ...)Argument Definitions
- index_num – The position number of the item you want returned.
- value1 – The first value in the list.
- value2 – Optional additional values you want Excel to choose from.
- … – Continue adding values as needed (up to 254 total values).
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> CHOOSE Function Data
Basic CHOOSE Function
In this example, Excel selects a department name based on a number.
Formulas Used
=CHOOSE(A2,"Sales","Finance","HR","IT")=CHOOSE(A3,"Sales","Finance","HR","IT")=CHOOSE(A4,"Sales","Finance","HR","IT")=CHOOSE(A5,"Sales","Finance","HR","IT")
How This Formula Works
- If the value is 1, return Sales
- If the value is 2, return Finance
- If the value is 3, return HR
- If the value is 4, return IT
Convert Month Numbers to Month Names
You can use CHOOSE to convert numbers into month names.
Formulas Used
=CHOOSE(A2,"January","February","March","April")=CHOOSE(A3,"January","February","March","April")=CHOOSE(A4,"January","February","March","April")=CHOOSE(A5,"January","February","March","April")
How This Formula Works
Excel uses the month number to determine which month to return from the list.
- 1 = January
- 2 = February
- 3 = March
- 4 = April
This works well when you only need a limited set of categories.
Assign Employee Performance Ratings
CHOOSE works well for turning numeric scores into descriptive labels.
Formulas Used
=CHOOSE(A2,"Poor","Average","Good","Excellent")=CHOOSE(A3,"Poor","Average","Good","Excellent")=CHOOSE(A4,"Poor","Average","Good","Excellent")=CHOOSE(A5,"Poor","Average","Good","Excellent")
How This Formula Works
Excel uses the rating number to determine which performance category to return from the list.
- 1 = Poor
- 2 = Average
- 3 = Good
- 4 = Excellent
Return Numeric Bonus Percentages
The CHOOSE function can also return numbers.
Formulas Used
=CHOOSE(A2,0.02,0.05,0.08,0.10)=CHOOSE(A3,0.02,0.05,0.08,0.10)=CHOOSE(A4,0.02,0.05,0.08,0.10)=CHOOSE(A5,0.02,0.05,0.08,0.10)How This Formula Works
The formula returns a bonus percentage based on employee performance tier.
For example:
- Tier 1 = 2%
- Tier 2 = 5%
- Tier 3 = 8%
- Tier 4 = 10%
Common CHOOSE Function Mistakes
Using a Number Outside the Available Options
If your index number is larger than the number of choices available, Excel returns an error.
Example:
=CHOOSE(5,"Red","Blue","Green")This returns an error because only 3 choices exist.
Starting Numbering at 0
CHOOSE starts at 1, not 0.
Incorrect:
=CHOOSE(0,"A","B","C")Correct:
=CHOOSE(1,"A","B","C")Using CHOOSE for Large Lookup Tables
CHOOSE works best with a small fixed list of values. For larger lookup tasks, functions like VLOOKUP, XLOOKUP, or INDEX MATCH are usually better options.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> CHOOSE Function Data
