The CHOOSE function in Excel is very versatile.  It can be used as a lookup and it can be combined with many other functions that make it extremely useful.

“Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number. For example, if value1 through value 7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.”

Source 

CHOOSE Syntax:

The syntax for the CHOOSE function in Excel is

=CHOOSE(index_number, value1, [value2],…)

Where

Index_number will be decided which value argument isselected.  This can be a number (between 1 and 254), a cell reference or even a formula.

Value1, [value2}, are the values to be returned based on the index number selected.

Simple Example of the CHOOSE function in Excel

CHOOSE will allow us to return a value based on anindex_num.  let’s say we had a requirement to return the day based on a user input.  By giving each day its own number, we can use the CHOOSE function.  For example, Monday will be index_num 1, Tuesday will be index_num 2

=CHOOSE(1, “Monday”,”Tuesday”,”Wednesday”, “Thursday”,”Friday”,”Saturday”,”Sunday”)

Where 1 is the selected index_num therefore this formula will return Monday

The formula can also be written by referencing the Values to cells.  For Example

=CHOOSE(2,A2,A3,A4,A5,A6,A7,A8)

Will return Tuesday as we have selected Index_num 2 and this has been defined as the contents of cell A3.

More Complex Choose in Excel Examples.

Lets take this a little deeper and look at more complex CHOOSE function in Excel examples.

The Index_number decides which value argument is selected.  This can be a number (between 1 and 254), a cell reference or even a formula. 
So far, we have hard coded this number into our CHOOSE function.  However, there are many ways this can be defined.

In this example we have a need for the user to select between a shipping type to return the shipping cost.

By using the CHOOSE function we could give Standard Post an index number of 1, Fast Delivery 2 and Express Delivery 3

By entering the index number into cell B7 we need to drive the formula in cell B10.

The CHOOSE formula would therefore be

=CHOOSE(B7,B2,B3,B4)

Now any time we change the value in cell B7, the CHOOSE function will update.

The problem with this is there is no way for the user to know what number you allocated to each shipping type and so this is prone to errors and does not really suite the need.  This can be overcome by adding a Form control.

Developer Ribbon

Form Controls are found in the developer ribbon.  If this ribbon is not available, you can easily add it to your ribbon.

Go to file, then options. In options select Customize Ribbon. Under Main Tabs, tick the box for Developer and your Developer ribbon
will become available.

Adding a Form Control

To add a form control to your worksheet, select Insert from the Developers Ribbon and then select List Box

Next, a box must be drawn on the worksheet.  This is your form control and it now needs to be activated.  To activate the form control, right click on the form control and select Format Control.

The Input range are the cells that contain the information you wish the user to select between.  In this example, the options are in cells A2:A4.

Cell Link is the output.  When an item is selected from the form control, the form control produces an output which is an index value. 
It works like the match function and returns the relative position in the list for the selection.  Therefore if Standard Post is selected, 1 will be returned as the output as this is the first position in the list.

To make the worksheet user-friendly, the Form control can be placed over cell B7 to hide the output.  The number has no meaning to the user and does not need to be displayed.

The CHOOSE function in cell B10 remains linked to cell B7 for the index number and so when a user changes the shipping option the shipping cost will also update.