SWITCH in Excel
SWITCH(expression, value1, result1, [value2, result2], ..., [default]) compares one expression with a list of values and returns the result that goes with the first match. An optional last argument is returned when nothing matches. It replaces a chain of nested IFs that all test the same cell.
Syntax
SWITCH(expression, value1, result1, [value2, result2], ..., [default])| Part | What it is |
|---|---|
expression | The value to test, usually a cell with a code or a category. |
value1, result1 | A value to compare with, and what to return when it matches. Up to 126 pairs. |
default | Optional. Returned when no value matches. It is the one argument left over after the pairs. |
Examples
=SWITCH(B2, "S", 9, "M", 19, "L", 49)Result: 19 when B2 holds M
With no default, a code that is not listed returns #N/A.
=SWITCH(B2, "S", 9, "M", 19, "L", 49, 0)Result: 0 for any code other than S, M or L
The odd argument at the end is the default.
=SWITCH(WEEKDAY(A2), 1, "Weekend", 7, "Weekend", "Weekday")Result: Weekend for a Sunday or Saturday date, Weekday otherwise
=SWITCH(TRUE, B2>=90, "A", B2>=80, "B", "C")Result: A, B or C by score
Testing TRUE turns SWITCH into a list of conditions, the same job as IFS.
Common mistakes
- #N/A means nothing matched and there is no default. Add a last argument for everything else.
- SWITCH only tests for equality. For ranges such as 80 or more, use IFS, or SWITCH(TRUE, ...).
- Text is compared without regard to case, but "M " with a trailing space does not match "M". Wrap the expression in TRIM.
- An even number of arguments after the expression means the last pair is read as value and result, with no default.
Practice SWITCH
These drills check your answer on live data, free, no account needed.
- Plan codes to pricesTurn each plan code into its monthly price with SWITCH: B is 9, P is 19, E is 49, and any other code is 0. Fill C2 to C4.
- Shipping tiersShipping costs 5 for parcels up to 1 kg, 12 for parcels up to 5 kg and 25 for anything heavier. Use IFS to fill C2 to C4.
Questions
SWITCH or IFS, which should I use?
SWITCH when one cell is compared with a list of exact values, because the cell is written once. IFS when each test is a different condition, such as score bands.
Can SWITCH return a default value?
Yes. Add one more argument after the last value and result pair. It is returned when no value matches.
Which Excel versions have SWITCH?
Excel 2019, Excel 2021 and Microsoft 365. In older versions use nested IF, CHOOSE or a small lookup table.
Related
- IFSHow the Excel IFS function checks several conditions in order and returns the first match, with grade and shipping tier examples and the fix for #N/A.
- IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.
- CHOOSEHow the Excel CHOOSE function returns one of several values based on an index number, with examples for weekday names, quarters and scenario switches.
- VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.