Microsoft Excel: CHOOSE() & OFFSET() function

CHOOSE () function

The CHOOSE function in excel returns a value from a list using the given index position of the element in the array and passing it as the parameter inside the CHOOSE function.

It extracts a value from a range/array, given the value’s index position.

Syntax

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

The CHOOSE function syntax has the following arguments:

• Index_num    Required. 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.
  • If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
  • If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
  • If index_num is a fraction, it is truncated to the lowest integer before being used.
• Value1, value2, …     Value 1 is required, subsequent values are optional. 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

Let’s implement the CHOOSE function in Excel to see an example use case:

We will be applying the given formula

The cell B2,B3& B4 will act as our index_num and the list passed is [“Apple”,”Mango”,”Banana”]

Which gives us the following output:

According to the indexes, correct value is returned from the List.

Notes to Remember

• If index_num is an array, every value is evaluated when CHOOSE is evaluated.
• The value arguments to CHOOSE can be range references as well as single values.

For example, the formula:

=SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))

evaluates to:

=SUM(B1:B10)

which then returns a value based on the values in the range B1:B10.

The CHOOSE function is evaluated first, returning the reference B1:B10. The SUM function is then evaluated using B1:B10, the result of the CHOOSE function, as its argument.

Some more examples of CHOOSE:

• =CHOOSE(2,A2,A3,A4,A5)

Returns Value of the second list argument (value of cell A3)

• =CHOOSE(4,B2,B3,B4,B5)

Returns Value of the fourth list argument (value of cell B5)

• =CHOOSE(3,”Wide”,115,”world”,8)

Returns Value of the third list argument i.e. “world”.

• =SUM(A2:CHOOSE(2,A3,A4,A5))

Sums the range A2:A4. The CHOOSE function returns A4 as the second part of the range for the SUM function.

OFFSET function in Excel

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Syntax

OFFSET(reference, rows, cols, [height], [width])

The OFFSET function syntax has the following arguments:

• Reference    Required. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
• Rows    Required. The number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
• Cols    Required. The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
• Height    Optional. The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
• Width    Optional. The width, in number of columns, that you want the returned reference to be. Width must be a positive number.

For example, applying the following formula

=OFFSET(D3,3,-2,1,1)

Our reference is D3 from which we are going 3 rows below and 2 columns to the left.

Which will land us on the cell B6 and the value of the B6 will be returned to the selected cell. See the output.

We have the following result:

Using

=OFFSET(D3, -3, -3)

Returns an error, because the reference is to a non-existent range on the worksheet.