CHOOSE

Chooses a specified value from a given set of values

Syntax of Excel CHOOSE formula:
=CHOOSE(index_num,value1,[value2],…)

In words:
=Choose(this value from, this value1, or this value2, and other values mentioned so on…)

Explanation

index_num: is the number we have to provide. It can be a hard-coded figure or a cell address that has a number or another function that helps get a number. So it all boils down to a numbah! And based on this number function will return the value which we mention in the next argument.

  • It can be any number between 1 and 254 (including 1 and 254). It cannot be less than 1 or greater than the number of options/values mentioned otherwise it will return a #VALUE error.
  • It cannot be 0. If 0 is given as an argument then it will return #VALUE error.
  • If it is a fraction or not a whole number then it is reduced to the lowest integer but it must be not less than 1.

value 1, [Value 2], [Value 3]: are the packages or options that we provide to Excel. It has to be at least one option for Excel to choose from that is why Value 1 is mandatory but later ones are optional. This formula can hold up to 254 options/packages/values.

  • It can either be a text, number or a range of cells.
  • It can be a named range (you might have to wrap the CHOOSE function in another function to get it to work. Thanks to Erdol. Check the comment section for an example.)
  • It can have a formula or a function.

Excel CHOOSE function in detail: Understanding Excel CHOOSE() function [Intro]

Examples of Excel CHOOSE Formula

=CHOOSE(2,100,200,300,400,500,600,700,800,900,1000) will give 200 as the second value is 200.

=CHOOSE(1,SUM(B2:B5),AVERAGE(B2:B5),MAX(B2:B5),MIN(B2:B5)) will return the sum of range B2:B5 as first value requires SUM function to be performed.