Excel CHOOSE: Select Values by Index
CHOOSE picks a value from a list based on an index number. It's a cleaner alternative to nested IF statements for fixed options.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Return the quarter name based on the month number"
=CHOOSE(ROUNDUP(MONTH(A2)/3, 0), "Q1", "Q2", "Q3", "Q4")MONTH extracts the month number (1-12). Dividing by 3 and rounding up converts it to 1-4 (quarter index). CHOOSE then picks the matching quarter name.
Step-by-step breakdown
- CHOOSE(index_num, value1, value2, ...) selects by position
- MONTH(A2) gets the month number (1-12)
- Dividing by 3 and ROUNDUP gives quarter index: 1, 2, 3, or 4
- CHOOSE picks Q1/Q2/Q3/Q4 based on that index
- Supports up to 254 values
Edge cases & warnings
- Index must be between 1 and the number of values — 0 or out-of-range returns #VALUE!
- CHOOSE evaluates ALL values, not just the selected one — avoid expensive calculations in unused positions
- For more than 5-6 options, consider XLOOKUP or INDEX/MATCH instead
- CHOOSE can return ranges: =SUM(CHOOSE(index, A:A, B:B, C:C))
Examples
"Month: January (1)"
Q1"Month: August (8)"
Q3"Month: December (12)"
Q4Frequently asked questions
When should I use CHOOSE vs IF?
Use CHOOSE when selecting from a fixed list by index number (1, 2, 3...). Use IF for conditional logic with varying conditions. CHOOSE is cleaner for 3+ fixed options.
Can CHOOSE return ranges?
Yes. =SUM(CHOOSE(1, A:A, B:B)) sums column A. This is useful for dynamic formula switching.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.