How to Use CHOOSE Function in Excel for Scenarios

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

• Suppose, you have the following dataset which contains 3 scenarios for you to decide which one your company should use. 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

• Enter the following formula in cell C10 and drag the Fill Handle icon to the right. You will see #VALUE! as no index_num is available in cell $B$10 as referred by the formula.

Step 3 – Enter Specified Index Number for Scenarios to Get the Particular Result

• Enter any number between 1 and 3 in cell B10 as there are only 3 scenarios. You will see the value related to that scenario as follows.

• 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

Related Articles

• How to Use Excel Formula to Choose Between Two Values
• How to Use CHOOSE Function with Array in Excel
• Use CHOOSE Function to Perform IF Condition in Excel
• How to Use VLOOKUP with CHOOSE Function in Excel
• Advanced Uses of CHOOSE Function in Excel

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