CHOOSE
Picks a value from a short list by its number: a delivery method by code, a quarter name by its number.
CHOOSE(index_num, value1, [value2], ...)- [ ]optional part
- index_num
- The number of the option you need: 1, 2, 3… Usually a reference to a cell with a code.
- value1
- The first option: the function returns it when the number is 1.
- value2
- The second option and the rest, separated by commas.Optional
Return value
The value from the list with the given number: the second one for 2. If the number is less than 1 or greater than the number of values, you get #VALUE!.
Examples
Delivery method by code
fx
A
B
C
D
1
2
3
4
5
6
7
Order
Delivery code
Method
5101
2
5102
1
5103
3
5104
2
Sheet1
Details
CHOOSE gets a number and a list of options right in the formula. If the number is 1, the function returns the first option; if it's 2, the second, and so on.
Options are separated by commas, and text goes in quotes. This is handy when there are only a few options and they don't change: for a long list, a separate table with VLOOKUP works better.
Common mistakes
- The number is less than 1 or greater than the number of options in the list. The function returns #VALUE!.
- Text options without quotes. Excel treats them as names, not text, and the formula returns an error.