CHOOSE Function in Excel
CHOOSE picks a value from a list based on a numeric index. It's Excel's version of a switch statement — clean, compact, and available in every version since forever. Underrated because it looks simple, but combines beautifully with WEEKDAY, MONTH, and MATCH to solve problems VLOOKUP can't.
What it does: Returns the Nth value from a list of arguments, where N is index_num. =CHOOSE(2, "Red", "Green", "Blue") returns "Green". Supports up to 254 values.
What CHOOSE does
CHOOSE takes an index number and picks a value from the arguments that follow. Index 1 returns the first value, index 2 the second, and so on. If the index doesn't match any position (0, negative, or too high), CHOOSE returns #VALUE!.
Its power is that the values can be anything — numbers, text, cell references, ranges, or even other formulas. This makes CHOOSE useful for far more than just selecting from a static list.
CHOOSE + WEEKDAY. Since WEEKDAY returns 1–7 for a date, =CHOOSE(WEEKDAY(A2), "Sun","Mon","Tue","Wed","Thu","Fri","Sat") gives the day name — no lookup table required. Same trick works with MONTH for month names, and with INT for tier-based rules.
Syntax breakdown
index_num Required
The position to return, from 1 to 254. Must be a whole number — decimals are truncated (2.9 becomes 2). Zero, negative, or an index larger than the value list returns #VALUE!.
value1 Required
The value returned when index_num is 1. Can be any value — text, number, reference, range, or expression. All values in the list can be different types.
value2, value3, ... Optional
Additional values corresponding to indexes 2, 3, and so on, up to 254 total. Not all values need to be the same type — CHOOSE happily mixes text and numbers and ranges.
5 real-world examples
Example 1: Convert a day number to a day name
Cell A2 contains a date. Get the day name:
Result: Wednesday (if the date is a Wednesday). No lookup table needed, no locale issues, works everywhere.
Example 2: Quarter from month
Get "Q1", "Q2", "Q3", or "Q4" based on a month number:
=CHOOSE(MONTH(A2), "Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")Result: Quarter label. Verbose but crystal clear — reading the formula you can see exactly which months map to which quarter.
Example 3: Tier-based commission rate
Tier stored in A2 as 1, 2, or 3. Different commission rates:
Result: Sale amount times the appropriate rate. When rates change, edit the CHOOSE — no lookup table to maintain elsewhere.
Example 4: Left-lookup replacing INDEX/MATCH
VLOOKUP can only look right. CHOOSE can build a virtual left-lookup:
=VLOOKUP(A2, CHOOSE({1,2}, C:C, B:B), 2, FALSE)Result: Look up value in C:C, return corresponding value from B:B (to the left). CHOOSE constructs a virtual 2-column table with the order reversed. Classic pre-XLOOKUP pattern.
Example 5: Randomized selection for testing
Pick a random status label for test data:
=CHOOSE(RANDBETWEEN(1, 3), "Active", "Pending", "Cancelled")Result: One of the three status labels chosen at random. Useful for building sample datasets. Warning: RANDBETWEEN is volatile, so this recalculates on every sheet change.
Common errors and how to fix them
The index is 0, negative, or larger than the number of values provided. Common cause: a MATCH returned #N/A which propagates. Wrap in IFERROR or check the index with an IF: =IF(A2>=1 AND A2<=3, CHOOSE(A2,...), "N/A").
If index_num comes from a MATCH that returned #N/A, CHOOSE will surface that error. Fix at the source: =CHOOSE(IFERROR(MATCH(...), 1), value1, value2).
Usually because your index is coming through as text like "2" instead of the number 2. CHOOSE happily accepts text-numbers in some cases but not in array constants. Force numeric with VALUE() or multiply by 1.
CHOOSE truncates decimals to integers — 2.9 becomes 2, not 3. If you want rounding, wrap the index in ROUND: =CHOOSE(ROUND(A2, 0), ...).
CHOOSE vs alternatives
| Function | Best for | Trade-off |
|---|---|---|
| CHOOSE | Small fixed list picked by number | Index must be an integer 1-N |
| SWITCH | Matching a value against options | Excel 2019+; more flexible than CHOOSE |
| IFS | Multiple conditional branches | Excel 2019+; verbose for many options |
| VLOOKUP / XLOOKUP | Larger lookup tables | Overkill for 3-5 items; needs a table |
| Nested IF | Old Excel compatibility | Ugly and error-prone past 3 levels |
Version compatibility
Download the practice workbook
Every example above, plus CHOOSE + MATCH left-lookup patterns and month/quarter helpers.
Related functions
Frequently asked questions
Should I use CHOOSE or SWITCH?
SWITCH is more flexible — it matches any value (text, number) against options and returns the corresponding result. CHOOSE requires an integer index from 1 to N. Use CHOOSE when your input is already a small integer (like WEEKDAY or MONTH result). Use SWITCH when you're mapping arbitrary values ("Small", "Medium", "Large" → prices).
What's the maximum number of values CHOOSE supports?
254 values plus the index_num — so 255 total arguments. In practice, if you need more than a dozen, use a lookup table with VLOOKUP or XLOOKUP instead. Long CHOOSE formulas are hard to maintain.
Can CHOOSE return a range or array?
Yes — the values can be ranges. =CHOOSE(A2, B:B, C:C, D:D) returns column B, C, or D depending on A2. This is how the left-lookup trick with VLOOKUP works. Since Excel 2021's dynamic arrays, this pattern extends to array constants.
Why does CHOOSE with a decimal index truncate instead of rounding?
Because Excel converts index_num to an integer by truncation, not rounding. 2.9 becomes 2, not 3. To round, wrap the index: =CHOOSE(ROUND(index, 0), ...). Or use INT for consistent truncation behavior explicitly.
How does CHOOSE compare to nested IF?
CHOOSE is dramatically cleaner when the input is a small integer. =CHOOSE(A2, "Low", "Med", "High") vs. =IF(A2=1,"Low",IF(A2=2,"Med",IF(A2=3,"High"))). Same result, one line vs. three, and no ambiguity about the mapping. Prefer CHOOSE anytime the input is already an integer position.
Is CHOOSE faster than VLOOKUP for small lists?
Yes, noticeably. CHOOSE has no table search — it goes directly to the Nth argument. For 3-5 options, CHOOSE is both faster and clearer than a VLOOKUP against a lookup table. For 10+ options, a proper lookup table becomes cleaner despite CHOOSE being faster.
Can I use CHOOSE with array formulas?
Yes — CHOOSE was one of the first functions to accept array constants naturally. =SUMPRODUCT(CHOOSE({1,2,3}, A:A, B:B, C:C)) sums three columns. In modern Excel with dynamic arrays, CHOOSE with an array index returns multiple values that spill into cells.
Build cleaner selectors and lookups — with the Sheets & Cells AI Add-in
Describe your decision logic in plain English and the Add-in writes the right selection function — CHOOSE for numeric index, SWITCH for value matching, or a lookup table when the list is too long. All inside Excel.
Learn about the Add-in →