The Excel CHOOSE function picks a value from a list by position: index 1 returns the first value, index 2 the second, and so on. It turns numbers into labels, dates into quarters, and — its best trick — a single cell into a scenario switch that swaps entire ranges in and out of other formulas.
#VALUE!.
Syntax
| Argument | Description | |
|---|---|---|
index_num | Required | Which value to return: 1 picks value1, 2 picks value2, and so on. Must work out to a number between 1 and the count of values; decimals are truncated (2.9 means 2). |
value1 | Required | The first value in the list. Can be a number, text, cell reference, range, or even a formula. |
value2, … | Optional | Up to 254 values in total. Each one is only evaluated if it’s the one picked. |
Values can be ranges, not just labels. =SUM(CHOOSE(2, A1:A10, B1:B10)) sums B1:B10 — CHOOSE hands the whole range to SUM. This is what makes CHOOSE a scenario switch, not just a label picker.
Turn numbers into labels
The classic use: a rating of 1–4 becomes a tier name. The table below maps James Chen’s rating of 2 to its label:
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Employee | Rating | Rating | Tier | |
| 2 | James Chen | 2 | 2 | Silver | |
| 3 | Maria Lopez | 4 | |||
| 4 | Devon Smith | 1 | |||
| 5 | Priya Patel | 3 |
CHOOSE shines whenever a calculation already produces a small integer. Two favorites — the fiscal quarter of a date, and the weekday name:
Try it: interactive CHOOSE demo
Slide index_num and watch CHOOSE walk the list — including what happens when the index runs past the end.
The power move: switch entire ranges
Because each value can be a range, one cell can flip a whole model between scenarios. With Best/Expected/Worst forecasts in columns B, C, and D, a single selector cell drives every downstream formula:
The old-school trick of feeding CHOOSE an array constant lets VLOOKUP look left by rebuilding the table with columns swapped:
How does CHOOSE compare to its siblings?
| Function | Picks by | Best when |
|---|---|---|
CHOOSE | Position (1, 2, 3, …) | The selector is already a small integer; values may be ranges. All Excel versions. |
SWITCH | Matching a value ("East", "West", …) | The selector is text or arbitrary values, with a default fallback. Excel 2019+. |
INDEX | Position within a range | The list lives on the worksheet instead of inside the formula — easier to maintain. |
Errors & common pitfalls
#VALUE! — index out of range. index_num is less than 1 or greater than the number of values you listed. A rating of 5 against a 4-item list returns #VALUE!, not a blank.
#NAME? — unquoted text values. =CHOOSE(2, Bronze, Silver) fails because Excel reads the words as names. Text values need quotes.
Pitfall: decimals are truncated, not rounded. An index of 2.9 picks value 2, not value 3. If your selector comes from a division, wrap it in ROUND or ROUNDUP deliberately.
Pitfall: the list is buried in the formula. Twenty CHOOSE formulas with the same hard-coded labels means twenty edits when a label changes. For long or changing lists, put the values in a range and use INDEX instead.
Practice workbook
Frequently asked questions
What's the difference between CHOOSE and SWITCH?
Can CHOOSE return a range instead of a single value?
=SUM(CHOOSE(2, A1:A10, B1:B10)) hands the entire range B1:B10 to SUM. One selector cell can switch a whole model between scenario columns.What happens if index_num is a decimal?
How many values can CHOOSE hold?
How do I pick a random item from a list?
=CHOOSE(RANDBETWEEN(1,4), "North", "South", "East", "West") returns a random region on every recalculation.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class