how to use excel choose function

Get a value from a list.

CHOOSE Function can help you to get a value from a list based on its position. In simple words, you can call a value from a list by specifying the position of that value.

For example, if you have created a list of months (Jan to Dec) and if you mentioned position 5 it will return “May” in a result.

How To Use Excel Choose Function

In above example, I have used CHOOSE Function with an option button to select the type of the service to calculate the shipping cost.

Syntax

CHOOSE(index_num,value1,value2,…)

index_num A number for specifying the position of the value in the list.

value1 A range of cells or an input value from which you can choose.

[value2] A range of cells or an input value from which you can choose.

More Information on Excel CHOOSE Function

  • It can handle up to 254 values.
  • You can refer to a cell or you can also insert values directly in the function.

Examples

In below example, I am using CHOOSE Function with a drop-down list to calculate four(sum, average, max, and mix) different things. I have used below formula to calculate the all four things with CHOOSE Function.

=CHOOSE(VLOOKUP(K2,Q1:R4,2,FALSE),SUM(O2:O9),AVERAGE(O2:O9),MAX(O2:O9),MIN(O2:O9))

Use Excel CHOOSE Function With Drop Down List

 

I have created a small table with the name of all four calculations which I want and inserted a serial number to each in the corresponding cell.

After that, I have created a drop down list for all four calculations.

Now, to get index number in choose function from that small table I have created a lookup formula which will return serial number as per the value selected in drop down list.

And, instead of values, I have used four formulas for 4 different calculation. Whenever you select calculation type with drop down list it will change the calculation.

Sample File

Download Sample File To Learn Excel CHOOSE Function

What’s Next?

To learn more about Excel CHOOSE Function you can check Microsoft’s Help Section. And, if you have a unique idea to use the choose function, I would love to hear from you.

You also have MATCH  function which is highly useful like choose function. Apart from this, I have a list of excel functions and some real life formulas examples.