Excel Tutorials · Lesson 9
SWITCH takes one value, compares it against a list of possible matches, and returns the result tied to whichever one it finds first. It replaces the tangle of nested IF statements most people write for simple lookups like status codes, department names, grade letters and abbreviations. This lesson covers the full syntax, the optional default, and the exact point where SWITCH stops being the right tool.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
The SWITCH function evaluates a single expression against a list of values you supply, and returns the result that corresponds to the first value it matches. Written out, the syntax is =SWITCH(expression, value1, result1, [value2, result2], ..., [default]). The expression is the cell or formula you want to test, each value/result pair is one possible match and what to return for it, and the optional default at the end catches anything that didn't match at all.
The reason SWITCH exists is readability. Before it arrived, converting a short code into a full word meant nesting IF statements inside each other, and a five-option conversion turned into a formula with five closing parentheses at the end that nobody could safely edit six months later. SWITCH flattens that same logic into a single readable line where each option sits next to its result, so adding a sixth option means typing two more arguments instead of restructuring the whole formula.
The tradeoff, and it matters, is that SWITCH only performs exact equality matching. It asks whether the expression equals value1, then whether it equals value2, and so on. It cannot ask whether a number is greater than 90, or whether a date falls inside a range, or whether text contains a substring. The moment your logic needs a comparison rather than an exact match, SWITCH is the wrong function and IFS or nested IF is the right one.
SWITCH is available in Excel 2019, Excel 2021, Excel 2024 and all versions of Microsoft 365, on Windows, Mac and Excel for the web. It is also available in Google Sheets, where it behaves identically. If you open a workbook containing SWITCH in Excel 2016 or earlier, the formula returns a #NAME? error, which is the single most common compatibility complaint about the function.
Step-by-step, matching the video above.
Identify the single cell or expression that holds the value you want to convert, a status code, a one-letter abbreviation, a department number, a day-of-week number, and make sure it holds the same kind of value in every row.
Type =SWITCH( in the cell where you want the converted result, then click the cell holding the value you want to test and add a comma. That first argument is your expression.
Type the exact value to match, then a comma, then what to return when it matches. Text values need quotation marks on both sides, numbers do not. For example, =SWITCH(A2,"P","Pending".
Continue adding value/result pairs separated by commas, one pair for every possible outcome you want to name. Excel highlights each argument as you type, and the pairs must always stay in twos or the formula will misread which argument is which.
Finish with one extra argument that has no matching value in front of it. That lone final argument is the default, returned whenever the expression matches nothing at all. Without it, an unmatched value returns #N/A.
Type the closing parenthesis and press Enter. If your value/result pairs are unbalanced, Excel will refuse the formula and point at the argument count, which is nearly always a missing comma or an extra one.
Double-click the fill handle in the bottom-right corner of the cell to copy the formula down the whole column. Because SWITCH references a single relative cell, each row tests its own value automatically.
| A: Code | B: =SWITCH(A2,"P","Pending","A","Approved","D","Denied","Unknown") |
|---|---|
| P | Pending |
| A | Approved |
| D | Denied |
| X | Unknown |
| (blank) | Unknown |
Three value/result pairs handle the three real codes, and the lone final argument, "Unknown", catches everything else including blanks and typos. Written as nested IFs, the same logic reads =IF(A2="P","Pending",IF(A2="A","Approved",IF(A2="D","Denied","Unknown"))), three functions, three closing parentheses, and considerably more room to make a mistake.
Four argument types, and knowing which is which is most of the battle.
The first argument, and the only one that is always required. Usually a single cell reference, but it can be any formula that produces a value, such as WEEKDAY(A2) to convert a date into a day number before matching it against names of days.
Each value is what the expression is compared against, using exact equality. Text values must be wrapped in quotation marks, numbers must not be. A number stored as text will not match a numeric value argument, which is a common invisible failure.
What gets returned when the value immediately before it matches. Results can be text, numbers, cell references, or entire formulas, so a SWITCH can return a calculation rather than a label if you want it to.
A single trailing argument with no value paired in front of it. Excel identifies it purely by position, if the total argument count after the expression is odd, the last one is treated as the default. Leave it out and unmatched values return #N/A.
SWITCH accepts up to 126 value/result pairs, far more than any readable formula should ever need. Once you are past roughly a dozen, a lookup table with XLOOKUP is a better structure than a formula.
Three functions that overlap, with one clear rule for choosing between them.
When you are converting one specific value into another, codes to labels, numbers to names, abbreviations to full text, SWITCH is the shortest and clearest option because you name the expression once and never repeat it.
When your conditions involve greater than, less than, ranges or multiple different cells, IFS is the right function. =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"F") handles grade bands that SWITCH simply cannot express.
If the workbook has to open in Excel 2016 or earlier, neither SWITCH nor IFS exists there, and nested IF is the only version that will calculate rather than showing #NAME? on every row.
A fast way to decide: if writing the logic as an IFS means typing the same cell reference over and over, SWITCH will be shorter. If each condition tests something different, or tests a range rather than a value, stay with IFS.
Where this function actually earns its place in a real workbook.
Systems export short codes, humans read words. SWITCH turns a column of P/A/D or 1/2/3 into Pending, Approved and Denied without a lookup table sitting on another tab.
Wrap WEEKDAY or MONTH inside SWITCH: =SWITCH(MONTH(A2),1,"Jan",2,"Feb",3,"Mar","Later"). This is the classic case where the expression is a formula rather than a plain reference.
Because results can be numbers, SWITCH can return a rate, a budget figure or a multiplier tied to each category, then feed that number straight into a larger calculation.
Point SWITCH at a cell controlled by data validation so a user's selection returns a different metric, label or range name, giving you a small dashboard switcher with one formula.
SWITCH can normalize a handful of known misspellings into one correct value, useful before you build a PivotTable that would otherwise treat each variant as a separate category.
| A: Date | B: =SWITCH(WEEKDAY(A2,2),6,"Weekend",7,"Weekend","Weekday") |
|---|---|
| 2026-09-04 | Weekday |
| 2026-09-05 | Weekend |
| 2026-09-06 | Weekend |
| 2026-09-07 | Weekday |
WEEKDAY with a second argument of 2 numbers the days Monday through Sunday as 1 through 7, so matching only 6 and 7 catches Saturday and Sunday. Everything else falls through to the default. The expression does not have to be a bare cell, any formula that resolves to a single value works.
Same syntax, same behavior, one difference in version support.
Google Sheets uses =SWITCH(expression, case1, value1, [case2, value2], ..., [default]), which is the same structure under different argument names. Formulas copy between the two applications without editing.
Because Sheets is always current, there is no equivalent of the Excel 2016 #NAME? issue. A shared Sheet works for everyone regardless of what they have installed locally.
Sheets lets you wrap SWITCH in ARRAYFORMULA to convert an entire column with a single formula in the header row, instead of filling the same formula down every row.
The four things that break a SWITCH formula, and what each one looks like.
This means the expression matched none of your values and there was no default. Add a trailing argument, or wrap the whole thing in IFNA to substitute your own message.
The version of Excel opening the file does not have SWITCH, which means Excel 2016 or older. Rewrite as nested IF, or ask everyone to update.
A blank expression will not match a value of "" reliably. If blanks need their own label, test for them separately with an IF wrapped around the SWITCH.
SWITCH is not case sensitive, so "p" and "P" both match a value of "P". That is usually helpful, but it means SWITCH cannot distinguish between two codes that differ only in capitalization.
A value of "Approved " with a trailing space will never match "Approved". Wrap the expression in TRIM to strip stray spaces before comparison.
SWITCH reads arguments strictly in pairs after the expression. A missing comma or an extra one shifts everything, and a value you intended as a match silently becomes a result, or your default disappears.
Typing P instead of "P" makes Excel look for a named range called P rather than the letter, which returns #NAME? even on a version that fully supports SWITCH.
SWITCH only performs exact equality. Grade bands, budget thresholds and date ranges need IFS or nested IF, no arrangement of SWITCH arguments will produce a comparison.
Without a trailing default argument, any value not explicitly listed returns #N/A. Always add a catch-all unless you specifically want unmatched rows to flag themselves.
SWITCH names the tested value once, at the start. Referencing that same cell again inside the results is redundant and usually means the logic belongs in IFS instead.
Past about a dozen options the formula becomes unmaintainable. Put the pairs into a two-column range on another sheet and use XLOOKUP, which is easier to update and easier to audit.
A status shouldn’t need a formula to become a word.
Free 14-day trial. No credit card required.
SWITCH compares one expression against a list of values and returns the result paired with the first value that matches exactly, with an optional default for anything that matches nothing.
=SWITCH(expression, value1, result1, [value2, result2], ..., [default]). The expression comes first, then value and result arguments in pairs, then an optional lone default at the end.
Add one extra argument at the very end with no value paired in front of it. Excel treats that trailing odd argument as the default and returns it whenever nothing matches.
The tested value matched none of the values you listed and no default was supplied. Add a trailing default argument, or check for extra spaces and inconsistent capitalization in the source data.
The version of Excel opening the file does not include SWITCH. It requires Excel 2019, 2021, 2024 or Microsoft 365, so Excel 2016 and earlier will not recognize the function name.
SWITCH matches one expression against exact values, while IFS evaluates a series of separate logical tests. Use SWITCH for code-to-label conversion and IFS for comparisons like greater than or less than.
For exact-match conversions, yes, it is shorter, easier to read and easier to extend, because you name the tested value once instead of repeating it inside every nested layer.
No. SWITCH only tests for exact equality. Any logic involving ranges, thresholds or comparisons needs IFS or nested IF instead.
Up to 126 value and result pairs, though past roughly a dozen a two-column lookup table with XLOOKUP is far easier to maintain than a long formula.
No, SWITCH matches without regard to capitalization, so a lowercase entry will match an uppercase value argument and vice versa.
Yes. Any formula that resolves to a single value works, which is how WEEKDAY or MONTH gets wrapped inside SWITCH to convert date numbers into day or month names.
Yes, with identical syntax and behavior, and without the version compatibility issue that affects older desktop versions of Excel.
Yes. Result arguments can be text, numbers, cell references or entire formulas, so SWITCH can return a rate or multiplier that feeds directly into another calculation.
Blanks do not reliably match an empty string value, so test for them separately with an IF wrapped around the SWITCH, or rely on the default argument to label them.
Every business starts with a spreadsheet. Updoot is where you scale past it, with statuses that already read like statuses.
Start Your Free Trial →