SWITCH compares one value against a list of possible matches and returns the corresponding result — cleaner than nested IFs for exact-match lookups.
SWITCH takes one value and compares it against a series of possible matches, returning the result tied to whichever match succeeds first.
It is specifically designed for matching one value against several exact options — like converting a numeric grade code into a label — and reads more clearly than a long chain of nested IFs for that exact use case.
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])| Argument | Description |
|---|---|
| expression required | The value to compare against each option. |
| value1, result1 required | The first value to match, and what to return if it matches. |
| value2, result2, ... optional | Additional match/result pairs. |
| default optional | What to return if nothing matches. Without this, no match returns #N/A. |
=SWITCH(A2,1,"Low",2,"Medium",3,"High","Unknown")If A2 is 2, returns "Medium". If A2 is anything not listed, returns "Unknown" via the default.
=SWITCH(WEEKDAY(A2),1,"Sun",2,"Mon",3,"Tue",4,"Wed",5,"Thu",6,"Fri",7,"Sat")Converts a WEEKDAY result directly into a readable day name.
=SWITCH(A2,1,"Low",2,"Medium")If A2 is anything other than 1 or 2, this returns #N/A since no default was given.
=IF(A2=1,"Low",IF(A2=2,"Medium",IF(A2=3,"High","Unknown")))Produces the same result as the SWITCH example above, but reads less clearly as more options are added.
IFS evaluates a list of independent conditions and returns the result for the first one that is TRUE — useful for ranges and comparisons (score>90, score>80, etc).
SWITCH compares one single value against a list of exact options — better suited to matching codes, categories, or specific values rather than ranges.
Without one, any value that does not match returns #N/A rather than a graceful fallback.
SWITCH only matches exact values — for "greater than" style logic, use IFS or nested IF instead.
ExcelPro has 700+ hands-on Excel exercises across 7 career tracks — free to start, no download needed.
Start practicing free →