“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.”
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.
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 |
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
What formula did you use to return the selling price?
What formula did you use to return the Units sold?
What 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!
Power Query Excel 365
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
SIGN UP FOR OUR NEWSLETTER AND GET EXCEL & POWER BI TIPS TRICKS AND TUTORIAL TO YOUR INBOX
SIGN UP NOW
Sign up for my newsletter – Don’t worry, I won't spam. Just useful Excel and Power BI tips and tricks to your inbox
SIGN UP NOW
Cross posted from my blog with SteemPress : http://theexcelclub.com/the-choose-function-in-excel/