Related Function:

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

  • Think of the CHOOSE function as a function that returns the nth entry in a given list.

Syntax

=CHOOSE(index_num,value1,[value2], … )

Note: Beginning with Excel 2007, you can enter up to 254 [value] arguments to the function. Excel 2003 would only accept up to 29 [value] arguments.

Arguments

Argument Description
index_num Specifies which value argument is selected

  index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254
[value1],
[value2], …
1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. [value1] is required, subsequent values are optional

  The arguments can be numbers, cell references, defined names, formulas, functions, or text

Note: Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index_num. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

Usage note: The 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

Examples

  A B C D
1 Data      
2 1 Nails    
3 2 Screws    
4 3 Nuts    
5 Finished Bolts    
6        
7 Formula Result Notes
8 =CHOOSE(2,A2,A3,A4,A5) 2 Value of the second list argument (value of cell A3)
9 =CHOOSE(4,B2,B3,B4,B5) Bolts Value of the fourth list argument (value of cell B5)
10 =CHOOSE(4,A2,A3,A4,A5) Finished Value of the fourth list argument (value of cell A5)
11 =SUM(A2:CHOOSE(2,A3,A4,A5)) 6 Sums the range A2:A4. The CHOOSE function returns A4 as the second part of the range for the SUM function

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the supplied index_num is less than 1
  the supplied index_num is greater than the supplied number of values
#NAME? Occurs if any of the value arguments are text values that are not enclosed in quotes and are not valid cell references