Purpose

Return value

Syntax

=CHOOSE(index_num,value1,[value2],...)
  • index_num - The value to choose. A number between 1 and 254.
  • value1 - The first value from which to choose.
  • value2 - [optional] The second value from which to choose.

Using the CHOOSE function

The CHOOSE function returns a value from a list using a given position or index. The values provided to CHOOSE can be hard-coded constants or cell references. The first argument for the CHOOSE function is index_num . This is a number that refers to subsequent values by index or position. The next arguments, value1 , value2 , value3 , etc. are the values from which to choose from. Choose can handle up to 254 values. However, CHOOSE will not retrieve an item from inside range or array constant provided as a value. For larger sets of data in a table or range, INDEX and MATCH is a better way to retrieve a value based on position.

Examples

The formulas below use CHOOSE to return the 2nd and 3rd values from a list:

CHOOSE(2,"red","blue","green") // returns "blue"
CHOOSE(3,"red","blue","green") // returns "green"

Above, “blue” is the second value, and “green” is the third value. In the example shown in the screenshot, the formula in cell C5 is:

CHOOSE(B5,"red","blue","green") // returns "red"

CHOOSE will not retrieve values from a range or array constant . For example, the formula below will return a #VALUE error:

=CHOOSE(2,A1:A3)  // returns #VALUE

This happens because the index number is out of range. In this case, the required syntax is:

=CHOOSE(2,A1,A2,A3)

To retrieve the nth item from a range, use INDEX and MATCH . CHOOSE can be used to provide a variable table to a function like VLOOKUP:

=VLOOKUP(value,CHOOSE(index_num,rng1,rng2),2,0) // variable table

Notes

  • If index_num is out of range, CHOOSE will return #VALUE
  • Values can also be references. For example, the address A1, or the ranges A1:10 or B2:B15 can be supplied as values.
  • CHOOSE will not retrieve values from a range or array constant .

Purpose

Return value

Syntax

=COLUMN([reference])
  • reference - [optional] A reference to a cell or range of cells.

Using the COLUMN function

The COLUMN function returns the column number of a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. COLUMN takes just one argument, called reference , which can be empty, a cell reference, or a range. When no reference is provided, COLUMN returns the column number of the cell which contains the formula.

Examples

With a single cell reference, COLUMN returns the associated column number:

=COLUMN(A1) // returns 1
=COLUMN(C1) // returns 3

When a reference is not provided, COLUMN returns the column number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 4:

=COLUMN() // returns 4 in D6

When COLUMN is given a range, it returns the column numbers for that range:

=COLUMN(E4:G6) // returns {5,6,7}

In Excel 365 , which supports dynamic array formulas , the result is an array {5,6,7} that spills horizontally into three cells, starting with the cell the formula resides in. In earlier Excel versions, the first item of the array (5) will display in one cell only.

To get Excel 365 to return a single value, you can use the implicit intersection operator (@):

=@COLUMN(E4:G6) // returns 5

This @ symbol disables array behavior and tells Excel you want a single value.

Notes

  • Reference can be a single cell address or a range of cells.
  • Reference is optional and will default to the cell in which the COLUMN function exists.
  • Reference cannot include multiple references or addresses.
  • To get row numbers, see the ROW function .
  • To count columns, see the COLUMNS function .
  • To lookup a column number, see the MATCH function .