How to Use the CHOOSE Function in Google Sheets
=CHOOSE(index, choice1, [choice2, ...]). =CHOOSE(2, "A", "B", "C") returns B.
What CHOOSE does
CHOOSE turns a number into a value: 1 becomes the first choice, 2 the second, and so on. It is neat when a code such as a rating or a weekday number should become a word, and it replaces a long chain of IF or SWITCH tests.
CHOOSE syntax
=CHOOSE(index, choice1, [choice2, ...])
index: which choice to return. If it is zero, negative or larger than the number of choices, CHOOSE returns a#NUM!error.choice1: required. A value to return, either a literal or a cell reference.choice2, ...: optional further values.
Google's page says the function accepts up to 29 choices, while its examples section says up to 30 values. The two figures do not match, so if you need many choices, test the limit yourself.
A step-by-step example
Cell A2 holds a rating code, 3.
| A | |
|---|---|
| 1 | Rating code |
| 2 | 3 |
=CHOOSE(A2, "Low", "Medium", "High")
This returns High, because 3 is the third choice. The page's own sample is CHOOSE(2, "A", "B", "C"), which returns B.
Common mistakes
- An index out of range. 0, a negative number or a number above the count gives #NUM!.
- Counting from 0. The first choice is 1.
- Using it for ranges of numbers. CHOOSE matches exact positions. For bands, use IFS.
Try it yourself
Use the cell above (A2). Type your formula into the box, starting with =, then press Check.
Exercise 1: Code to word
Write a formula that turns the code in A2 into Low (1), Medium (2) or High (3).
Exercise 2: Second choice
Write a formula that returns the second of the choices "Red", "Green", "Blue".
What to learn next
- INDIRECT: build a cell reference from text.
- SWITCH: match one value against a list of cases.
- IFS: test several conditions in order without nesting.
Syntax and rules in this guide were checked against Google's CHOOSE help page. See all guides.