How to Use CHOOSE in Excel
Picks one value from a list by position — a compact alternative to nested IF for fixed options.
What is CHOOSE in Microsoft Excel?
CHOOSE returns the nth item from a list you supply. It is ideal when the options are fixed and few: quarters, weekdays, grade bands, or a set of named ranges. Combined with MATCH it becomes a dynamic selector that returns any value from a predefined list.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| index_num | Required | A number from 1 to 254 that selects which value to return. |
| value1 | Required | The value returned when index_num is 1. |
| value2 | Optional | The value returned when index_num is 2, and so on up to 254 values. |
When to Use CHOOSE (Real-World Business Scenarios)
- 1
Mapping a month number to a quarter name without a lookup table.
- 2
Selecting between several named ranges so a chart or formula can switch scenarios.
- 3
Replacing deeply nested IF statements when there are only a handful of fixed outcomes.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Month No | Quarter |
|---|---|
| 1 | Q1 |
| 4 | Q2 |
| 11 | Q4 |
How it works: B2 holds 1, so CHOOSE returns the first item in the list. Change B2 to 11 and the formula would need a different approach, since 11 exceeds the list length.
Common Errors & How to Fix Them
Why it happens: index_num is larger than the number of values supplied, or it is not a number.
Fix: Make sure index_num falls between 1 and the number of values. For ranges of numbers use MATCH or IFS instead.
Why it happens: CHOOSE is 1-based, so an index of 0 is invalid and off-by-one errors are common.
Fix: Double-check that your first option maps to 1, not 0, and that the list order matches the index source.
Try CHOOSE Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (CHOOSE)
What does the CHOOSE formula do in Excel?
CHOOSE returns the nth item from a list you supply. It is ideal when the options are fixed and few: quarters, weekdays, grade bands, or a set of named ranges. Combined with MATCH it becomes a dynamic selector that returns any value from a predefined list.
What is the syntax for CHOOSE?
What are common mistakes when using CHOOSE?
#VALUE!: index_num is larger than the number of values supplied, or it is not a number. (Make sure index_num falls between 1 and the number of values. For ranges of numbers use MATCH or IFS instead.) Returns the wrong option: CHOOSE is 1-based, so an index of 0 is invalid and off-by-one errors are common. (Double-check that your first option maps to 1, not 0, and that the list order matches the index source.)
Related Excel Formulas
Keep building your formula toolkit with these lookup & reference functions.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
- VLOOKUPThe industry-standard vertical lookup function that searches the first column of a table and returns a value in the same row.
- INDEX & MATCHThe robust two-part lookup formula that looks left, looks up, and never breaks when new columns are inserted.
- MATCHReturns the position of a value inside a range — the engine behind INDEX MATCH and most advanced lookups.
- HLOOKUPThe horizontal twin of VLOOKUP: searches across the top row of a table and returns a value from a row below it.
- XMATCHThe modern replacement for MATCH with exact-match default, reverse search and wildcard support built in.
Master CHOOSE in Intermediate Excel
Learn CHOOSE along with our formula library, practice files, and verifiable certificates in our complete curriculum.