CHOOSE

Returns a value from a list, given an index number.

Syntax:

CHOOSE(index, value1, value2, ... value30)

value1 ... value30 are up to 30 values, each of which may be text, a number, a logical value, a reference or a formula.

index is an index number specifying which of value1 ... value30 to return; 1 returns value1, 2 returns value2, etc.

Example:

CHOOSE(1, "dog", "cat")

returns the text dog.

CHOOSE(2, "dog", 65.4)

returns the number 65.4.

CHOOSE(3, "dog", 65.4, B5)

returns the reference B5, so that CHOOSE(3, "dog", 65.4, B5) in a cell shows the contents of cell B5 in that cell (it is equivalent to B5).

SUM(CHOOSE(1, B4:B5, B6))

returns the sum of the numbers in the range B4:B5.

CHOOSE(2, SUM(B1:C1), SQRT(9))

returns 3, the square root of 9.

Application:

Let's consider an application of the CHOOSE function: A retail company wants to calculate the bonus for its employees based on the number of years they have worked for the company. The bonus structure is as follows:

1 year of service: $500 bonus
2 years of service: $1,000 bonus
3 years of service: $1,500 bonus
4+ years of service: $2,000 bonus

Here is an employee data table:

	Employee Name	Years of Service
	A	B
1	John Doe	2
2	Jane Smith	4
3	Peter Jones	1
4	Mary Williams	3
5	David Brown	5

To calculate the bonus for each employee, we can use the CHOOSE function in the "Bonus" column.

Formula:

CHOOSE(MIN(B1,4), 500, 1000, 1500, 2000)

Explanation:

B1 is the cell containing the "Years of Service" for John Doe.
MIN(B1,4) is used as the index number. This ensures that even if an employee has more than 4 years of service (like David Brown with 5 years), the CHOOSE function will only look at the first four values and select the fourth one. This is crucial because CHOOSE can only select from the number of values you provide. If we didn't use MIN, the formula would return an error for employees with more than 4 years of service.
500, 1000, 1500, 2000 are the values from which CHOOSE will select.
- 500 is the 1st value.
- 1000 is the 2nd value.
- 1500 is the 3rd value.
- 2000 is the 4th value.

Resulting Table:

After applying the formula to the "Bonus" column, the table will look like this:

	Employee Name	Years of Service	Bonus
	A	B	C
1	John Doe	2	$1,000.00
2	Jane Smith	4	$2,000.00
3	Peter Jones	1	$500.00
4	Mary Williams	3	$1,500.00
5	David Brown	5	$2,000.00