Excel Choose Function

It is a built-in lookup/ reference function that is used to get the value using a giving position or index. Also, it can be used as a worksheet function and a VBA function in excel.

Syntax

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

Arguments

• Index_num. REQUIRED. The position number in the list of values to return. A number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254. Limit of 29 in EXCEL 2003.
• Value, Value2… Value1 is required, subsequent values are optional. A value can be a number, a cell reference, a defined name, a formula/function, or a text value. 1 to 254 value arguments from which Choose selects the a value or an action to perform based on index_num.

Purpose

To get a value from a list based on position

Return Value

The value at the given position

Return Type

string, numeric, date, etc.

Remarks

• 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.
• If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
• 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.

So, to get the value from the list using position, we use CHOOSE function. However, to get the position of a value in a row/ column/ table, use MATCH Function. Choose can handle up to 254 values but INDEX, or INDEX with MATCH are better approaches for large value sets.

Sources

• https://support.office.com/en-us/article/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc
• https://exceljet.net/excel-functions/excel-choose-function