CHOOSE Function

How to Use CHOOSE Function in Excel?

The Excel “CHOOSE” function is a built-in feature used to pick a certain value from a selected range.

Given a range and a specified position, the function will return the cell value that corresponds to the number input.

In practice, the CHOOSE function is one method to integrate scenario analysis into a financial model.

For instance, most financial models contain various cases to evaluate a company under different sets of assumptions.

At a bare minimum, most models contain three different types of cases.

1. Base Case → The scenario that is most likely to occur, in which the operating assumptions are kept conservative.
2. Upside Case → The most optimistic scenario that often coincides with the projections provided by management (or their advisors), i.e. reflects the “best case” scenario.
3. Downside Case → The scenario wherein the company’s performance misses projections, often used to assess the state of a company in the “worst case” scenario (or a similarly negative outcome with pessimistic assumptions).

The option to select different cases creates a more practical financial model given the uncertainty involved in forecasting.

Aside from the CHOOSE function, two other Excel functions often used as part of scenario analysis are the following:

1. OFFSET / MATCH Function
2. XLOOKUP Function

Excel CHOOSE Function Formula

The formula for using the CHOOSE function in Excel is as follows.

• “index_num” → Specifies which of the following value arguments to return, and is an integer that can range from 1 to 254
• “value1” → Required argument that can be a number, range, cell reference, formula, or text
• “value2” → Optional argument that could be a number, range, cell reference, formula, or text

CHOOSE Function Calculator – Excel Template

We’ll now move on to a modeling exercise, which you can access by filling out the form below.

You’re logged in! Form submission not required. Access your file below.

Download File

1. Excel CHOOSE Function Scenario Analysis Example

Suppose we’re tasked with integrating three different operating scenarios into a financial model.

The three different operation scenarios will be those mentioned earlier, i.e. the “Base Case”, “Upside Case” and “Downside Case”.

Our first step is to create a cell that will function as the “toggle” to switch between the different cases.

Once chosen, we’ll also name the cell “Case” by selecting the cell and typing in “Case” into the active cell box on the top far left corner.

While not applicable to our model, naming cells can make entering formulas into more complex financial models more efficient, although it should not be overdone.

Next, to ensure that anyone that views the model can easily tell which operating scenario is currently active, we’ll create a drop-down list to select the case.

• “1” → Base
• “2” → Upside
• “3” → Downside

In the cell below – while an optional step – we’ll enter an “IF” function to show the specific operating case running.

2. CHOOSE Function in Excel Calculation Example

Thus far, we've created a row that contains our hypothetical company’s historical (and projected) revenue figures, along with a section at the bottom that lists the assumptions from which to pick.

We’ll forecast revenue for the next five periods, using the following set of revenue growth assumptions.

• Base Case = 3.0% YoY
• Upside Case = 5.0% YoY
• Downside Case = (2.0%) YoY

For the sake of simplicity, the assumptions for the periods that come after Year 1 of the forecast will be linked to the initial assumption (i.e. straight-lined and maintained across the projection period).

On top of our assumptions table, we'll enter the following "CHOOSE" formula to select the correct revenue growth assumption.

After doing so, we’ll enter the following formula into our Year 1 (2022E) revenue cell.

Since our operating case switch is currently set at “1”, our starting revenue of $100 million increases by 3.0% from the prior period.

If set to “2”, the revenue would grow by 5.0% YoY under our upside case assumption or decline by 2.0% if set to “3” under our downside case assumption.

In closing, our model has now integrated three different scenarios using the CHOOSE function in Excel.

Turbo-charge your time in Excel

Used at top investment banks, Wall Street Prep’s Excel Crash Course will turn you into an advanced Power User and set you apart from your peers.

Learn More