Exceljet

Quick, clean, and to the point

Excel CHOOSE Function

Excel CHOOSE function
Summary 

The Excel CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.

Purpose 
Get a value from a list based on position
Return value 
The value at the given position.
Syntax 
=CHOOSE (index_num, value1, [value2], ...)
Arguments 
  • 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.
Usage notes 

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.

Example

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 B5 is:

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

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 an item from inside a value provided as a range or array constant.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.