SWITCH compares one value against a list of exact possible matches and returns the result tied to the first match. It is cleaner than a long nested IF chain when you are translating codes, numbers, or labels into readable output.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac. Excel 2016 and earlier do not include SWITCH, so use nested IF there.
Write a basic SWITCH formula
Use this for exact matches such as day numbers, status codes, grades, or category labels.
- Click the result cell.
- Start with =SWITCH( and choose the cell to test.
- List each possible value followed by the result to return.
- Add one final default result at the end if nothing matches.
- Close the parenthesis and press Enter.
Example: =SWITCH(A2,1,”Monday”,2,”Tuesday”,3,”Wednesday”,”Unknown day”). If A2 is 2, Excel returns Tuesday. If no listed value matches, it returns the default.
Use nested IF in older Excel
Build the same logic manually when SWITCH is unavailable.
- Use a pattern such as =IF(A2=1,”Monday”,IF(A2=2,”Tuesday”,IF(A2=3,”Wednesday”,”Unknown day”))).
- Each IF checks one value and passes unmatched values to the next IF.
- Use the final argument as the catch-all result.
Nested IF can produce the same result, but it becomes harder to read and edit as the list grows.
More control
Add a default result
Without a default result, SWITCH returns #N/A when nothing matches. A final value such as “Unknown” keeps reports cleaner.
Do not use SWITCH for ranges
SWITCH checks exact matches. It does not understand strings such as “>90” as comparisons. Use IFS or nested IF for greater-than, less-than, or between logic.
SWITCH versus IFS
Use SWITCH when one cell is being compared with fixed values. Use IFS when each condition is its own logical test.
Text matching ignores case
SWITCH treats Monday, monday, and MONDAY as the same text for normal matching.
Sources
- Microsoft Support – SWITCH function (2025)
- Microsoft Support – IF function – nested formulas and avoiding pitfalls (2025)
- Microsoft Support – Overview of formulas in Excel (2025)