CHOOSE
Pick a value from a list by its position number with Excel's CHOOSE function.
Spreadsheet editor
Spreadsheet editor
Syntax
=CHOOSE(index_num, value1, [value2], ...)
Returns: Varies Arguments
| Argument | Required | Description |
|---|---|---|
| index_num | Yes | The position of the value to return, from 1 to 254. Fractions are truncated to a whole number. |
| value1 | Yes | The first value to choose from. Can be a number, text, cell reference, or formula. |
| value2, ... | No | Up to 253 more values to choose from. Only the one matching index_num is returned. |
About
CHOOSE returns one value from a list based on a position number. You give it an index number, then up to 254 values to pick from. If the index is 1, it returns the first value; if it is 3, it returns the third, and so on. The values can be text, numbers, cell references, or even other formulas.
Use CHOOSE when you have a small, fixed set of options and a number that points to the one you want. It is handy for turning a code into a label, like converting 1, 2, 3 into Q1, Q2, Q3, or mapping a weekday number into a day name. Because each option is listed out in the formula, it works well when the choices rarely change.
For larger or changing lists, a lookup table is usually a better fit, so use VLOOKUP, XLOOKUP, or INDEX with MATCH instead. CHOOSE shines when the options are few and you would rather keep them inside one formula than build a separate table.
Use CHOOSE when you have a small, fixed set of options and a number that points to the one you want. It is handy for turning a code into a label, like converting 1, 2, 3 into Q1, Q2, Q3, or mapping a weekday number into a day name. Because each option is listed out in the formula, it works well when the choices rarely change.
For larger or changing lists, a lookup table is usually a better fit, so use VLOOKUP, XLOOKUP, or INDEX with MATCH instead. CHOOSE shines when the options are few and you would rather keep them inside one formula than build a separate table.
Exercises using CHOOSE
Examples
Decimals in the index get truncated
Watch what happens with a fractional index: the 2.9 acts like 2 and returns Banana, not Cherry. Edit the index to confirm only the whole number counts.
Spreadsheet editor
Catch out-of-range codes with IFERROR
See how an index of 5 or 0 makes plain CHOOSE return a #VALUE! error, while the IFERROR version shows a friendly message. Try other codes to compare the two columns.
Spreadsheet editor
Watch out for
⚠
Index outside the list returns an error
If index_num is less than 1 or larger than the number of values you listed, CHOOSE returns a #VALUE! error. A 0 or a blank index does the same.
→ Make sure your index always lands between 1 and the number of options. Wrap the formula in IFERROR to show a friendly message if a stray value slips through.
⚠
Fractions get truncated
CHOOSE chops any decimal off the index before using it, so an index of 2.9 is treated as 2, not rounded up to 3. This can return the wrong option without warning.
→ Feed CHOOSE a whole number. If your index comes from a calculation, control the rounding yourself with ROUND or INT first.
Tips & notes
CHOOSE can return cell references, not just plain values, so something like =SUM(CHOOSE(2,A1:A10,B1:B10)) totals whichever column the index points to. The index can also be an array, which lets CHOOSE feed multiple results into other functions in more advanced formulas.
Common questions
What is the maximum number of values CHOOSE can handle?
Up to 254 values. The index number must point to one of them, so it has to be between 1 and the number of options you provide.
Why does CHOOSE return a #VALUE! error?
Usually the index number is out of range. If it is below 1 or higher than the number of values listed, CHOOSE cannot find a match and returns #VALUE!.