Switch Financial Scenarios (Bear, Base, Bull) with CHOOSECOLS & INDEX

Three illustrative scenarios, one consistent selector

The matrix contains three annual cash-flow amounts per case. All amounts are in thousands of the same currency: a stored 150 means 150 thousand currency units. Bear, Base and Bull are illustrative labels, not predictions or assigned probabilities.

This fixed workbook maps Bear to column 1, Base to 2 and Bull to 3. H2 normalizes the selector’s case and outer spaces and rejects unknown choices, including wildcard characters. H3 verifies the expected header layout and all three selected numeric values. Do not reorder columns without updating the mapping.

A general header-driven model can use MATCH(selector,headers,0), but must also validate unique headers and account for wildcard criteria. Here the explicit mapping prevents an ambiguous duplicate header or wildcard selection from silently choosing another scenario.

=IF(COUNTBLANK(F2)>0,"Choose a scenario",IF(NOT(ISTEXT(F2)),"Choose a scenario",SWITCH(UPPER(TRIM(F2)),"BEAR",1,"BASE",2,"BULL",3,"Unknown Scenario")))

Total the selected column

H7 uses CHOOSECOLS to select the complete column and SUM to aggregate its three years. Missing or text amounts in the selected case produce a message. Incomplete amounts in an unselected case do not block the current case. Zero and negative cash flows are valid.

This is an undiscounted sum, not NPV or IRR. Those calculations need additional assumptions about timing, discount rates and initial investment; selecting a column does not provide them.

=IF(H3<>"Ready",H3,SUM(CHOOSECOLS(B2:D4,H2)))

Read each year with INDEX

H4, H5 and H6 use rows 1, 2 and 3 of the matrix with the same validated column index. The formula below returns Year 2. Base gives 150, 180 and 210, totaling 540 thousand units. Bear totals 360 and Bull totals 750.

=IF($H$3<>"Ready",$H$3,INDEX($B$2:$D$4,2,$H$2))

Inspect the whole selected vector

When H3 is Ready, CHOOSECOLS(B2:D4,H2) returns a three-row column. Place it in an empty area if you want a spilled vector. Keep the output area clear. The guarded INDEX outputs in this workbook remain readable when the selector or data is invalid.

=CHOOSECOLS(B2:D4,H2)

Further reading

• Microsoft Support — CHOOSECOLS function