SWITCH and CHOOSE Generator
Match a value against a list of cases with SWITCH and add a default, plus how CHOOSE picks a value by position instead.
SWITCH & CHOOSE
Match a value against a list of cases with SWITCH and add a default, plus how CHOOSE picks a value by position instead.
The cell or expression compared against each case below.
First value to match exactly. Text needs quotes; numbers do not.
Returned when the value equals case 1.
Second value to match exactly.
Returned when the value equals case 2.
Third value to match exactly.
Returned when the value equals case 3.
The lone trailing argument, returned when nothing matched. Leave it out and unmatched values return #N/A.
=SWITCH(A2, "N", "North", "S", "South", "E", "East", "Unknown")Worked Example
Turn a one-letter region code in A2 into a full region name, with a fallback for codes you have not mapped.
=SWITCH(A2, "N", "North", "S", "South", "E", "East", "Unknown")Returns: A2 containing S returns "South"; A2 containing W matches no case, so the trailing default returns "Unknown".
Checks Before You Paste
- •SWITCH only tests exact equality - it cannot do >= or <. For bands and ranges, either use IFS, or use the SWITCH(TRUE, ...) trick: =SWITCH(TRUE, A2>=90, "A", A2>=80, "B", "C") evaluates each condition and returns the result of the first that is TRUE.
- •The default is recognised purely by argument count: an odd argument at the end is the default, an even one starts a new pair. Delete a result by mistake and your intended default silently becomes a case value, which is the single most common SWITCH bug.
- •CHOOSE is the positional twin - =CHOOSE(2, "North", "South", "East") returns "South". Its index must land between 1 and 254; 0, a negative number, or an index past the last value returns #VALUE!, and a decimal like 2.7 is truncated down to 2.