CHOOSE Function
Purpose
Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number.
Syntax
EXCEL
=CHOOSE(index_num, value1, [value2], ...)
Arguments Breakdown
| Argument | Required? | Description |
|---|---|---|
index_num |
Required | Specifies which value argument is selected (an integer from 1 to 254, or a formula yielding an integer). |
value1 |
Required | The first value from which CHOOSE selects. |
value2, ... |
Optional | Additional value choices (up to 254 values). Can be numbers, cell references, text, formulas, or defined names. |
Practical Examples
1. Dynamic Fiscal Quarter from Month Number
EXCEL
=CHOOSE(MONTH(A2), "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", "Q4", "Q4")
2. Scenario Switcher (Best, Base, Worst Case Modeling)
In financial analysis, switching between 3 operational models:
EXCEL
=CHOOSE(ScenarioIndex, BestCaseRevenue, BaseCaseRevenue, WorstCaseRevenue)
3. The Classic "Left VLOOKUP" Trick (Legacy Workaround)
Before XLOOKUP, analysts used CHOOSE to swap table columns so VLOOKUP could look left:
EXCEL
=VLOOKUP(LookupVal, CHOOSE({1, 2}, ReturnCol, LookupCol), 2, FALSE)
(Modern best practice is to use XLOOKUP or INDEX/MATCH instead).
Related Knowledge
- Notes: 04 Lookup and Reference Functions, 03 Conditional Logic and Decision Making
- Concepts: VLOOKUP vs XLOOKUP
- Workbook:
11_Demos_and_Workbooks/03_Formulas_and_Functions/Formulas_&_Functions_Part_2.xlsx(Sheet:XLookup)