The Excel CHOOSE Function

Related Function:

MATCH Function

Choose and Match Functions

The Excel Choose and Match functions both work with arrays of data.

The Choose function receives a position and returns the value that has this position in the array.
The Match function receives a value to lookup, and returns the postion of the value in the array.

Function Description

The Excel Choose function returns a value from an array, that corresponds to a supplied index number (position).

I.e. the Choose function returns the n'th entry in a given list.

The syntax of the function is:

CHOOSE( index_num, value1, [value2], ... )

where the arguments are as follows:

index_num	-	An integer, which specifies the index of the item to be returned.
value1, [value2], ...	-	A list of one or more values that you want to return a value from. Note that these must entered as individual values (or references to individual cells containing values).

In current versions of Excel (2007 and later), up to 254 value arguments can be supplied to the Choose function. However, in Excel 2003, you can only supply up to 29 values to the function.

Excel Choose Function Examples

Example 1 - Using the Choose Function to Return a Value

Column A of the following spreadsheet shows the Choose function used to return the following colors, depending on the value of the index_num argument:

1 - red; 2 - blue; 3 - green; 4 - brown

Formulas:

	A
1	=CHOOSE( 4, "red", "blue", "green", "brown" )
2	=CHOOSE( 2, "red", "blue", "green", "brown" )
3	=CHOOSE( 3, "red", "blue", "green", "brown" )
4	=CHOOSE( 1, "red", "blue", "green", "brown" )

Results:

	A
1	brown
2	blue
3	green
4	red

Example 2 - Using the Choose Function to Return a Cell Reference

The Excel Choose function can also return cell references, as shown in the following example. In this case, the reference that is returned from the Choose function is then provided to the Excel SUM Function.

Formulas:

	A	B	C
1	10	3	=SUM( CHOOSE( B1, A1, A1:A2, A1:A3, A1:A4 ) )
2	11
3	12
4	13

Results:

	A	B	C
1	10	3	33
2	11
3	12
4	13

In the example above, the Choose function returns the cell reference A1:A3. This is then passed to the SUM function which calculates the sum of the values in the cell range A1:A3 and returns the value 33.

For further information and examples of the Excel Choose Function, see the Microsoft Office website.

Choose Function Errors

If you get an error from the Excel Choose Function, this is likely to be one of the following:

Common Errors

#VALUE!

Occurs if either:

The supplied index_num is less than 1 or is greater than the supplied number of values;
The supplied index_num argument is non-numeric.

#NAME?

Occurs if any of the value arguments are text values that are not enclosed in quotes and are not valid cell references.

Return to the Excel Lookup and Reference Functions Page
Return to the List of All Built-In Excel Functions