Returns a value from a list based on an index number.

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

---

Return a value from a list of up to 254 values based on an index number. For example, =CHOOSE(2,"France","Germany","Italy") would return "Germany" since it is the 2nd country listed. Values used in the CHOOSE function can included references.

CHOOSE syntax requires an index number between 1 and 254 followed by the list of values to be considered.

---

CHOOSE examples.

In the examples below, the CHOOSE function is used to identify countries from a list based on their population rank and to add the populations of defined countries by embedding the CHOOSE function into a SUM function.

---

Syntax

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

The CHOOSE function consists of at least 2 arguments:

1. index_num. Required. The value to choose from the list. A number between 1 and 254.
2. value1. Required. The first value to choose.
3. value2. Optional. The second value to choose.
4. value.... Optional. Additional values to choose up to 254 in total.

---

Try it now!

• Enter your formulas into the grey cells.

---

Scope

CHOOSE can be applied to constants or cell references.

Number:	=CHOOSE(2,"France","Germany","Italy") || result is "Germany"
Cell reference:	=CHOOSE(C2,B2,B3,B4) || result is "Italy" where cell C2 contains the number 3 and cell B4 contains the text "Italy".

Caveats

• The index_num needs to be 1-254. If the index_num is outside of this 1-254 range (either negative, 0 or >254), the #VALUE! error is returned.
• If index_num is a fraction (e.g. 2.9), CHOOSE truncates the index_num to the lowest integer (e.g. 2) before applying it to the values list.
• If index_num is an array, every value is tested by CHOOSE.
• Values can be (a mix of) numbers, cell reference, defined names, formulas, functions or text.
• Values from a range or array constant cannot be retrieved. =CHOOSE(2,C2:C3) will return a #VALUE! error.
• Range references can be values. For example, =SUM(CHOOSE(2,A1:A3,B1:B3,C1:C3)) will return =SUM(B1:B3).

---

Related Functions

• INDEX uses an index to choose a value from a reference or array
• MATCH looks up values in a reference or array
• OFFSET returns a reference offset from a given reference
• VLOOKUP looks in the first column of an array and moves across the row to return the value of a cell

---

Functions Category

• Lookup & Reference

 

---