How to Pick from a List with CHOOSE in Excel

Switch between labels, ranges or whole scenarios with one number — the quickest scenario selector in Excel.

You will learn to

• Return a label from a fixed list by number
• Switch which range a SUM uses
• Combine CHOOSE with INDEX/MATCH for one line of the chosen scenario

How it works

CHOOSE(index_num, value1, value2, …) returns the item at position index_num. =CHOOSE(2,"Lean","Base case","Stretch") returns "Base case". The items can be numbers, text, cell references or whole ranges.

Because CHOOSE can return a range, it makes an easy scenario switch: =SUM(CHOOSE(G1,B2:B8,C2:C8,D2:D8)) totals the low, base or high column depending on the number in G1. Index numbers outside 1 to the number of items return #VALUE!.

=SUM(CHOOSE(G1,B2:B8,C2:C8,D2:D8))