SWITCH and CHOOSE Generator

How SWITCH & CHOOSE works

Availability: CHOOSE is in every version of Excel and in Google Sheets. SWITCH needs Excel 2019, Microsoft 365 or Excel for the web; Google Sheets has SWITCH. Excel 2016 and earlier show _xlfn.SWITCH and return #NAME?.

These two functions solve the same shape of problem from opposite ends. SWITCH matches a value against a list of cases and returns the result paired with the first case it equals. CHOOSE ignores values entirely and picks by position: give it 3 and it returns the third item in the list, whatever that item happens to be.

SWITCH is the readable way to translate codes into labels - region initials into region names, status flags into descriptions, weekday numbers into words. It compares with plain equality only. There is no >=, no BETWEEN, and no wildcards, so bands and ranges belong in IFS instead. Text comparison is case-insensitive, so the case "n" matches a cell containing N. Its default is not a named argument: it is simply a final argument with no partner, recognised by the fact that the argument count is even rather than odd. Omit it and an unmatched value returns #N/A.

CHOOSE takes a number from 1 up to 254 and returns the value in that position. Anything below 1 or beyond the last value supplied returns #VALUE!, and a decimal index is truncated toward zero, so 2.9 selects the second item. The index is usually the output of another function - MONTH, WEEKDAY, MATCH or RANDBETWEEN - which is where CHOOSE earns its place, since the list of results can be typed inline rather than kept in a lookup table.

CHOOSE has one property SWITCH does not: its values can be ranges, and it returns them as live references. That makes =SUM(CHOOSE(2, A2:A13, B2:B13, C2:C13)) a way to sum whichever column a control cell selects. Once a mapping grows past about ten entries, though, both functions become worse than a two-column table read with XLOOKUP, because a table can be edited by someone who does not want to open the formula bar.

Syntax

More worked examples

Common mistakes

CHOOSE index outside 1 to the last value

=CHOOSE(0, "A", "B", "C") and =CHOOSE(4, "A", "B", "C") both return #VALUE!. When the index comes from MATCH, a failed match returns #N/A and CHOOSE passes that straight through, so check the index on its own before blaming CHOOSE.

A decimal index silently truncates

=CHOOSE(A2/2, ...) with A2 = 5 gives an index of 2.5, and CHOOSE returns the second value without complaint. If the intent was to round, do it explicitly with ROUND or ROUNDUP so the behaviour is visible in the formula.

Expecting SWITCH to handle ranges

=SWITCH(A2, ">90", "A", ...) never matches, because SWITCH compares the literal text ">90" against A2 rather than evaluating it. For thresholds either use IFS, or use =SWITCH(TRUE, A2>=90, "A", A2>=80, "B", "C"), where each case is a comparison that evaluates to TRUE.

Deleting a result turns the default into a case

SWITCH decides what the last argument is purely by counting. Remove one result from the middle and the argument count flips, so the value you meant as a default is read as a case with no result - Excel then reports too few arguments, or worse, silently pairs the wrong things. Count the arguments in pairs after every edit.

Case sensitivity that is not there

=SWITCH(A2, "n", "North", "N", "New") can never reach the second case: the comparison is case-insensitive, so a cell containing N matches "n" first and returns "North". If upper and lower case must mean different things, use IFS with EXACT.