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.
CHOOSE Function in Excel: Syntax
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.
Follow the steps below to be able to choose from various scenarios using the CHOOSE function in Excel.
📌 Step 1: Creating Multiple Scenarios in Excel
- 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: Using CHOOSE Function to Choose a Scenario in Excel
- 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)
📌 Step 3: Enter Specified Index Number for Scenarios to Get the Particular Result
- 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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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.
