Get a value from a list based on position
=CHOOSE(index_num, value1, [value2], ...)| Parameter | Description |
|---|---|
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. |
The formulas below use CHOOSE to return the 2nd and 3rd values from a list:
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 C5 is:
CHOOSE(B5,"red","blue","green") // returns "red"CHOOSE will not retrieve values from a range or array constant. For example, the formula below will return a #VALUE error:
=CHOOSE(2,A1:A3) // returns #VALUEThis happens because the index number is out of range. In this case, the required syntax is:
=CHOOSE(2,A1,A2,A3)To retrieve the nth item from a range, use INDEX and MATCH. CHOOSE can be used to provide a variable table to a function like VLOOKUP:
=VLOOKUP(value,CHOOSE(index_num,rng1,rng2),2,0) // variable table