How to switch which column a formula uses with CHOOSE in Excel
Formula (Excel and Google Sheets)
=SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))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.
- Result on the example
- $289,250 in
G2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))Result289250
G12The value in G1.
B2:B76 rows × 1 col: $41,000, $39,800, $43,200, $44,100, …A range: 6 cells in one column.
C2:C76 rows × 1 col: $45,500, $44,100, $47,800, $49,250, …A range: 6 cells in one column.
D2:D76 rows × 1 col: $50,200, $48,900, $52,600, $54,300, …A range: 6 cells in one column.
CHOOSE(G1,B2:B7,C2:C7,D2:D7)6 rows × 1 col: $45,500, $44,100, $47,800, $49,250, …CHOOSE: Picks a value by its position in the list.- index_num
G12 - value1
B2:B76 rows × 1 col: $41,000, $39,800, $43,200, $44,100, … - value2
C2:C76 rows × 1 col: $45,500, $44,100, $47,800, $49,250, … - value2
D2:D76 rows × 1 col: $50,200, $48,900, $52,600, $54,300, …
- index_num
SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7))289250SUM: Adds numbers and ranges. Skips text and blanks.- number1
CHOOSE(G1,B2:B7,C2:C7,D2:D7)6 rows × 1 col: $45,500, $44,100, $47,800, $49,250, …
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(CHOOSE(G1,B2:B7,C2:C7,D2:D7)). We ran it in Google Sheets mode on the same data and it returns $289,250.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(INDEX(B2:D7,0,G1)) | $289,250 | INDEX with row 0 returns a whole column of a block. Simpler when the scenarios sit side by side. |
| =SUM(CHOOSECOLS(B2:D7,G1)) | $289,250 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Switch which column a formula uses with CHOOSE
G2, total the revenue of the scenario numbered in G1 (1 Low, 2 Base, 3 High).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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.