How to Use CHOOSE Function in Excel for Scenarios

Get FREE Advanced Excel Exercises with Solutions!

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:

CHOOSE(index_num, value1, [value2],…)

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.

scenarios


πŸ“Œ 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)

choose function in excel


πŸ“Œ 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.

enter index number

  • Now enter another index number in cell B10 to see how the values change.

choose scenarios in excel

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo