How to Use CHOOSE Function in Excel for Scenarios

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:

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.

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.

scenarios


📌 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)

choose function in excel

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.

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.

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.


Related Articles

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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo