The CHOOSE function in Excel

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

choose in excel

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.

choose in excel

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)

choose in excel

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

choose in excel

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 devleoper ribbon

Adding a Form Control

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

adding form control in excel

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.

formatting a control in excel

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.

form controls in excel

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.

Form Controls in Excel

Earn and Learn Activity – Using CHOOSE to select between different tables

Copy the following tables into Excel.  To do this just highlight all of the tables, right click and select copy.  On a new worksheet in excel, select paste.

Selling Price

  

Scenario 1

100

  

Scenario 2

110

  

Scenario 3

95

  
    

Units Sold

  

Scenario 1

700

  

Scenario 2

600

  

Scenario 3

900

  
    

Scenario

1

  

Revenue Calculation

  

Selling Price

   

Units Sold

   

Total Sales

   

We have been given two tables of data.  A selling price table and a units sold table. Under this we have a Scenario number and this number is to be used to drive
calculations in the Revenue calculations table.

For the selling price, if scenario 1 is selected, we require the selling price for scenario 1, which is 100. If 2 is selected, we want the selling price for scenario 2.

This could be solved using a nested IF Statement.  It would read IF 1 then 100, if 2 then 110 otherwise return 95.  However, if we use this CHOOSE function, we can write an easier to understand formula.

Your task: Use the CHOOSE function to return the selling price, the units sold and calculate the total sales based on the scenario
selected.

To earn STEEM rewards on this post, in the comment section below, answer the following questions

Tell me what formula did you use to return the selling price?

What is the formula used to return the Units sold?

Which formula did you use to return the total sales?

CHOOSE Examples 3 and 4

 This video will show you how to use CHOOSE to look up values from different tables and solve the problem in the Learn and Earn Activity.  If you have not yet carried out the activity there is still plenty of time.  Just answer the the questions in the comments below.

This video will also show you a forth example of the CHOOSE function in action and you will learn how to use CHOOSE function combined with VLOOKUP to return values from different worksheets.

Take A FREE course with us Today!

 

The Ultimate Excel Formulas Course

* includes XLOOKUP and will soon include Dynamic Arrays

Become a Power Pivot Hero

Power Pivot online training course

 

 

GET and TRANSFORM DATA like a PRO

Power Query Excel 365

 

 

Learn DAX for Power Pivot and Power BI

DAX for powerpivot course
 
 
 
The Excel Club is the only Excel Blog in the world where you can Earn while you Learn Excel.  Find out about our Learn and Earn Activities now

learn and earn excel activities

 

SIGN UP FOR OUR NEWSLETTER AND GET EXCEL & POWER BI TIPS TRICKS AND TUTORIAL TO YOUR INBOX

 

 

 

Best Value Excel and Excel Power Tool Learning.  Access All Areas, Unlimited Learning Subscription

 

Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox

%d bloggers like this: