# How to switch which column a formula uses with CHOOSE in Excel

Let CHOOSE pick the range: `=SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))` totals the Low, Base or High column depending on the number in G1.

**Excel:** `=SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Month | Low | Base | High |  | Scenario (1-3) | 2 |
| Jan | 41000 | 45500 | 50200 |  | Total revenue |  |
| Feb | 39800 | 44100 | 48900 |  |  |  |
| Mar | 43200 | 47800 | 52600 |  |  |  |
| Apr | 44100 | 49250 | 54300 |  |  |  |
| May | 45600 | 50900 | 56100 |  |  |  |
| Jun | 46300 | 51700 | 57500 |  |  |  |

Formula in G2: `=SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))`

Result: $289,250

## How it works

CHOOSE can return ranges, not just values. With 2 in `G1` it returns `C2:C7`, the Base column, and SUM adds it up to $289,250. Change G1 to 3 and the same formula totals the High column. This is the classic scenario switch in financial models.

## Other ways

- `=SUM(INDEX(B2:D7,0,G1))`: INDEX with row 0 returns a whole column of a block. Simpler when the scenarios sit side by side.
- `=SUM(CHOOSECOLS(B2:D7,G1))`: CHOOSECOLS does the same in Excel 2024, Microsoft 365 and Google Sheets.

## Common mistakes

- A number outside 1 to 3 returns #VALUE!. Restrict G1 with data validation.
- CHOOSE's advantage is that the ranges can be anywhere, even far apart. For adjacent columns, INDEX is shorter.

## Questions

**How do I choose the scenario by name?**

Turn the name into a number with MATCH: `=SUM(CHOOSE(MATCH(G1,{"Low","Base","High"},0),B2:B7,C2:C7,D2:D7))`.

**Can I use it for a lookup?**

Yes: `=XLOOKUP("Mar",A2:A7,CHOOSE(G1,B2:B7,C2:C7,D2:D7))` returns March in the chosen scenario.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/choose-range-by-number