Excel CHOOSE Function (Pick by Index) – Examples & Practice
Practice the Excel CHOOSE function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Use index 2 to return the second option from "North", "South", "West".
Formula Syntax
=CHOOSE(index_num, value1, [value2], ...)
- index_num: Which value to return (1 selects value1, 2 selects value2, …).
- value1: First choice (any type).
- [value2], ...: Up to 254 values in modern limits.
What it does
CHOOSE returns one value from a list based on a positive integer index. It is useful for compact branching when the number of options is small and stable.
Excel CHOOSE Function Examples
Pick a label by index
=CHOOSE(2, "North", "South", "West")
Returns South because index 2 selects the second value.
Branch metrics by quarter dropdown
=CHOOSE(Q1, Jan!B10, Feb!B10, Mar!B10) when Q1 is 1..3 (example layout—adjust to your model).
Guard invalid index
=IF(AND(A1>=1, A1<=3), CHOOSE(A1, 10, 20, 30), "Pick 1-3")
Keeps CHOOSE inside a safe range.
dynamic-array-data.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Category | Amount | Region |
| 2 | A | 120 | North |
| 3 | B | 340 | South |
| 4 | A | 560 | North |
| 5 | C | 225 | West |
| 6 | B | 90 | South |
| 7 | Output |
Input Formula
Need Help?
Tips
- Validate index cells with data validation lists.
- Prefer a lookup table when options grow beyond a handful.
- Pair with **IFERROR** for user-facing templates.
CHOOSE Function Use Cases
- Small scenario pickers (1–12 months, 3 regions)
- Legacy dashboards before SWITCH existed
- Teaching index-based logic
- Lightweight mapping without a lookup table
- Combine with MATCH for ordered categories
Common mistakes - CHOOSE function not working
- Off-by-one indexes from user dropdowns
- Too many arguments to maintain by hand
- Using CHOOSE when XLOOKUP to a table is clearer
- Forgetting #VALUE! on out-of-range indexes
- Mixing text and numeric returns without clear formatting
FAQ
What happens if index_num is out of range?
Excel returns #VALUE! when the index is smaller than 1 or larger than the number of value arguments.
Can CHOOSE return a range?
In some patterns CHOOSE can return references for functions that accept references—test carefully for your Excel version.
CHOOSE vs SWITCH?
CHOOSE maps small integers 1..N to values; SWITCH matches expressions to values without requiring consecutive indexes.
Can index_num be fractional?
It is truncated toward zero—avoid relying on this; use INT or ROUND intentionally if needed.
Is CHOOSE volatile?
CHOOSE itself is not volatile like RAND, but it recalculates with normal sheet recalculation.
Comparison
| Function | Best when |
|---|---|
| CHOOSE | Small integer maps |
| SWITCH | Many explicit matches |
| IFS | Ordered logical tests |
Example
=CHOOSE(2, "North", "South", "West")
Advanced examples
CHOOSE + MATCH on sorted bands
Map a numeric score to a tier label when tiers are enumerated as discrete CHOOSE branches (often clearer with a lookup table instead).
Dynamic sheet routing (advanced)
Some models use CHOOSE to pick among sheet references—document heavily; INDIRECT-style routing can be fragile.