CHOOSE function
CHOOSE picks one value from a list by its position number. It is handy for turning a code into a label, or for choosing between ranges.
Example
Read it as a sentence: use the number in F4 (TRUE, which counts as 1) to pick from the list a, b, which gives a.
Excel shows a
F
1
2
'123
3
2024-02-29
4
TRUE
Data
Syntax
CHOOSE(index_num, value1, [value2], ...)
Arguments in [square brackets] are optional.
-
index_num(required) - Which option to return, starting at 1.
-
value1(required) - First option.
-
value2(optional) - More options, up to 254.
Returns: The option at position index_num.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=CHOOSE(2,"a","b","c") | b | Basic |
=CHOOSE(2.9,"a","b","c") | b | Fractional index |
=CHOOSE("2","a","b") | b | Text index |
=CHOOSE({1,2},"a","b") | a | Array index spills |
=CHOOSE(1,"a",1/0) | a | Unchosen value is an error |
=CHOOSE(Data!F4,"a","b") | a | TRUE as index |
=CHOOSE(4,"a","b","c") | #VALUE! | Index past list |
=CHOOSE(0,"a","b") | #VALUE! | Index 0 |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
Data
Watch out for
- An index below 1 or above the list length gives #VALUE!.
- Options can be ranges, letting CHOOSE feed VLOOKUP a rearranged table.
- Index is truncated if decimal.
Where it works
- Excel
- CHOOSE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .