March 1, 2024

How to use the Excel CHOOSE function

Choose function in excel is used to fetch a value from a given range of data when we provide it an index number and the starting point of values, there are two mandatory arguments for this function and the index number and the first value is mandatory while the other values are optional to mention.

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.

With an example 1:

  • Syntax: =CHOOSE(2,”Samsung”,”Sophuc”,”Cafe”)
  • The result is: Sophuc

With an example 2:

  • Syntax: =CHOOSE(5,”Samsung”,”Sophuc”,”Cafe”)
  • The result is: #VALUE!

Example: Let’s look at some Excel CHOOSE function examples and explore how to use the CHOOSE function as a worksheet function in Microsoft Excel:

Excel CHOOSE function - How to use the Excel CHOOSE function

Syntax:  =CHOOSE(5,B2,B3,B4,B5,B6,B7,B8)


Excel CHOOSE - How to use the Excel CHOOSE function

Based on the Excel spreadsheet above, the following CHOOSE examples would return:

Syntax: =CHOOSE(5,B2:B8)
Result: #VALUE!

READ:  How to use max function in excel

Syntax: =CHOOSE(5,A2,A3,A4,A5,A6,A7,A8,B2,B3,B4,B6,B5,B7,B8)
Result: 2468

Note: The Excel CHOOSE function returns a value from a list using a given position or index. The values provided to CHOOSE can include references. However, CHOOSE will not retrieve an item from inside range or array constant provided as a value. Choose can handle up to 254 values but INDEX, or INDEX with MATCH are better approaches for large value sets.

Leave a Reply

Your email address will not be published. Required fields are marked *