In Excel: use =SWITCH(expression, case1, result1, case2, result2, …, default) — it matches one value against a list of exact cases.
Syntax
Arguments
| Argument | Description | |
|---|---|---|
expression | required | The value to compare against each case. |
value1 | required | The first case to match exactly. |
result1 | required | What to return when the first case matches. |
default | optional | An odd final argument becomes the fallback when nothing matches. |
Related functions
Confirm the mapping is exact-match — any "greater than" logic needs IFS instead.
Type =SWITCH( and enter the cell or expression to test.
Add each case and its result as a pair, separated by commas.
Add one final unpaired argument as the default for unmatched values.
What this does
SWITCH evaluates an expression once and compares it against a list of exact values, returning the result paired with the first that matches. Where IFS handles arbitrary conditions such as ranges, SWITCH is for exact-match mapping — a status code to a label, a country code to a region, an abbreviation to a full name. Its default is supplied by an unpaired final argument, which is more compact than IFS's trailing TRUE. It cannot express a range comparison at all, so anything involving "greater than" belongs in IFS. Requires Excel 2019 or later. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “excel switch formula”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a workflow that saves repeating the same clicks every week useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Mapping status codes: =SWITCH(B2, "P", "Pending", "A", "Approved", "R", "Rejected", "Unknown") returns a readable label, with "Unknown" as the fallback for anything else. Mapping a month number to a quarter: =SWITCH(ROUNDUP(A2/3,0), 1,"Q1", 2,"Q2", 3,"Q3", 4,"Q4"). SWITCH turns a code-to-label mapping into one readable line, and knowing its exact-match limit tells you when to reach for IFS instead. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Nothing on this page is behind a login: the tool runs entirely in your browser, the formula is shown in full with one-click copy, and the steps work the same on Windows and Mac. That is the whole promise here — the exact answer, a way to prove it on your own numbers, and just enough context to make it stick. If you take one thing from this page on “excel switch formula”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
Common mistakes
- Trying to express a range, which SWITCH cannot do — it compares for equality only.
- Losing count of the pairs, so the intended last result is read as the default.
- Building a long mapping in SWITCH where a lookup table plus
XLOOKUPwould be easier to maintain.
Frequently asked questions
When should I use SWITCH instead of IFS?
When you are matching one value against a list of exact possibilities. IFS is for conditions such as ranges.
How do I set a default?
Add a single unpaired argument at the end. If the argument count is odd, the last one is the default.
Is a lookup table better?
Usually, once the mapping exceeds about six cases — a table plus XLOOKUP can be edited without touching a formula.
Other ways people ask this
Platform & version notes
- Several of those searches mention Google Sheets — and the good news is that "excel switch formula" works there almost identically: the formula syntax carries over unchanged, only the surrounding menus sit across the top of the screen instead of in a ribbon.
Can I do this in Google Sheets too?
Yes. "excel switch formula" carries over to Google Sheets with the same formula and almost the same steps — the menus are arranged differently, but the result is identical.