The CHOOSE Function for Scenario Analysis in Excel

CHOOSE returns a value from a list based on an index number. =CHOOSE(2, a, b, c) returns b. In financial models it is the cleanest way to build a scenario switch: a single driver cell holds 1, 2, or 3, and CHOOSE pulls the base, bull, or bear assumption into the live model so the whole forecast flips with one input.

Syntax and the scenario pattern

The signature is =CHOOSE(index_num, value1, value2, ..., value254). The index_num must evaluate to a number between 1 and the count of values supplied. If it is 1, value1 is returned, if 2, value2, and so on. A non integer index is truncated, and an index outside the valid range returns #VALUE!.

For scenario analysis, you put a single switch cell somewhere visible, say $B$1, holding 1, 2, or 3. Every assumption that varies by case becomes a CHOOSE that reads that one switch, so the entire model responds to a single input.

Worked example: a three case revenue growth toggle

Suppose $B$1 is your scenario switch and you have three growth rates: base 8 percent, bull 15 percent, and bear 2 percent. You want the live growth assumption to follow the switch.

1. In the live assumption cell enter =CHOOSE($B$1, 0.08, 0.15, 0.02).
2. When $B$1 is 1, the formula returns 0.08 (base case).
3. Change $B$1 to 2 and it returns 0.15 (bull case); set it to 3 and it returns 0.02 (bear case).
4. Reference this live cell throughout the revenue build so flipping $B$1 reprices the entire forecast at once.

CHOOSE returns the value in the position matching the switch cell.

CHOOSE versus other scenario methods

CHOOSE keeps the candidate values inline, which is fast to write but hides the assumptions inside formulas. The cleaner alternative for larger models is to lay out base, bull, and bear in a labeled table and use INDEX or XLOOKUP to pull the active column, keeping all cases visible and auditable on the sheet.

Use CHOOSE when the cases are few and the values are simple, and graduate to an INDEX driven scenario block when you have many line items, because pulling a whole column with =INDEX(cases, , $B$1) scales better than repeating long CHOOSE formulas everywhere.

• CHOOSE: quick toggles, few cases, values acceptable inline.
• INDEX or XLOOKUP on a case table: many line items, full auditability, all cases visible.
• Avoid nested IFs for scenarios: they are harder to read and extend than a single switch.

Pitfalls to watch

Guard the switch cell. An index of 0, a blank, or a value above the case count returns #VALUE!, which then cascades. Use data validation to restrict $B$1 to 1, 2, or 3, or wrap with IFERROR to fall back to the base case.

Keep the case order consistent everywhere. If position 2 means bull in one formula and bear in another, the model will quietly mix cases. Document the mapping once near the switch cell so every CHOOSE follows the same order.