Md. Shamim Reza
This article illustrates how to apply the CHOOSE Function in Excel to various scenarios. The function can be very useful to choose between various scenarios especially in financial modeling to decide the optimum model. You can also combine this function with other Excel functions to carry out an operation (for example SUM, PRODUCT, etc.) for a specific scenario. Follow the article to see how to choose between scenarios using this function.
Download Practice Workbook
You can download the practice workbook from the download button below.
Introduction to Excel CHOOSE Function
The CHOOSE Function in Excel chooses a value or action to perform from a list of values, based on an index number.
Syntax:
Arguments:
- index_num: Required. Values between 1 and 254 included. Cannot be larger than the total number of arguments after this argument.
- value1: The CHOOSE Function will choose this value if the index_num is 1.
- value2, value3,…: The CHOOSE Function will choose any one of these values if the index_num is 2,3,… respectively.
Remarks:
- The index_num argument must be a number between 1 and 254.
- The index_num argument can’t be greater than the number of total values in the list. Otherwise, the function will return #VALUE!
- The index_num argument will be rounded down to the nearest integer if a fractional number is used.
- value1, value2,… arguments can be numbers, formulas, cell references, defined ranges, or texts.
How to Use CHOOSE Function in Excel for Scenarios
Follow the steps below to be able to choose from various scenarios using the CHOOSE function in Excel.
📌 Step 1: Create Several Scenarios in an Excel Spreadsheet
- Assume you have the following dataset. It contains 3 scenarios for you to decide which one your company should use. Now you can use the CHOOSE Function to pick one scenario at a time to analyze the advantages and disadvantages.
📌 Step 2: Apply Excel CHOOSE Function to Choose a Scenario
- Then, apply the following formula in cell C10 and then drag the Fill Handle icon to the right. Next, you will see #VALUE! as no index_num is available in cell $B$10 as referred by the formula.
=CHOOSE($B$10,C5,C6,C7)
Read More: Advanced Uses of CHOOSE Function in Excel (9 Applications)
📌 Step 3: Enter Specified Index Number for Scenarios to Get Result in That Particular Case
- Now any number between 1 and 3 in cell B10 as there are only 3 scenarios. After that, you will see the value related to that scenario as follows.
- Now enter another index number in cell B10 to see how the values change.
Read More: How to Apply CHOOSE Function to Create Drop-Down List in Excel
Things to Remember
- You must lock the cell reference i.e. use absolute reference for index_num.
- You must copy the formula to the adjacent cells to get all values corresponding to the selected scenario.
- You can create a dropdown list in cell B10 to quickly choose between the scenarios.
Conclusion
Now you know how to use the CHOOSE Function for scenarios in excel. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
