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.

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.


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


  • 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.


  • 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 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.


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

<< Go Back to Excel CHOOSE Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF