SWITCH Function in Excel and Google Sheets
Purpose
Compare an expression against a list of values and return the result paired with the first match
Return value
The result paired with the first matching value, or the default if no match is found, or #N/A if no default is provided
SWITCH is the function I reach for whenever a column of codes, numbers, or short labels needs to be translated into something human-readable. Before SWITCH, this was a job for nested IF or a lookup table — either a formula three levels deep or an extra sheet just to hold the mapping. SWITCH collapses all of that into a single flat formula. State the expression once, list the possible values and their corresponding outputs, add a default at the end for anything that does not match, and the translation is done. Department codes to department names. Status numbers to status labels. WEEKDAY numbers to day names. Anywhere you have a fixed set of known values mapped to known outputs, SWITCH is cleaner than IFS and far cleaner than nested IF. It is available in Excel 2019, Excel 365, and all versions of Google Sheets.
Syntax
✓ Excel 2019+
✓ Google Sheets
= Same syntax
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
Arguments
| Argument | Required | Description |
|---|---|---|
| expression | Required | The value to compare against each value argument. Evaluated once and compared to each value in sequence. Can be a cell reference, formula result, or literal value. |
| value1, result1 | Required | The first value to match and the result to return when expression equals value1. |
| value2, result2 ... | Optional | Additional match-result pairs, up to 126 pairs total. Evaluated in order — the first match wins. |
| default | Optional | A final unpaired argument returned when expression matches none of the listed values. Without it, SWITCH returns #N/A for unmatched values. |
How it works
SWITCH evaluates the expression once and then compares it to each value argument in order. The moment it finds a match it returns the paired result without evaluating the remaining pairs. If no value matches and a default argument is present, the default is returned. If no value matches and there is no default, SWITCH returns #N/A. Comparisons are exact — SWITCH does not support range conditions like >=90. For range-based classifications, IFS is the right choice. SWITCH is for exact-value mapping: a code column that should display a full name, a numeric status that should display a label, a WEEKDAY result that should display a day name. The default argument is the last argument in the list and is not paired with a match value — it stands alone. Always include a default to handle values you did not anticipate, otherwise unknown codes silently return #N/A.
Examples
1
Convert department codes to full department names, showing Unknown for any code not in the list.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Code | Department |
| 2 | EMP-001 | ENG | =SWITCH(B2,"ENG","Engineering","MKT","Marketing","SLS","Sales","HR","Human Resources","Unknown") |
| 3 | EMP-002 | MKT | Marketing |
| 4 | EMP-003 | SLS | Sales |
| 5 | EMP-004 | HR | Human Resources |
| 6 | EMP-005 | FIN | Unknown |
Row 2: Engineering
— B2 equals ENG which matches the first value, so Engineering is returned immediately without comparing against the remaining pairs.
EMP-005 has the code FIN which does not match any listed value, so the default Unknown is returned. Without that final default argument, FIN would produce #N/A.
2
Convert numeric order status codes to readable labels: 1 Processing, 2 Shipped, 3 Delivered, 4 Cancelled, anything else Unknown.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Status Code | Label |
| 2 | ORD-001 | 1 | =SWITCH(B2,1,"Processing",2,"Shipped",3,"Delivered",4,"Cancelled","Unknown") |
| 3 | ORD-002 | 2 | Shipped |
| 4 | ORD-003 | 3 | Delivered |
| 5 | ORD-004 | 4 | Cancelled |
| 6 | ORD-005 | 5 | Unknown |
Row 2: Processing
— Status code 1 matches the first value pair so Processing is returned.
ORD-005 has status code 5 which is not in the list — Unknown is returned via the default. If a new status code is introduced, simply add another value-result pair before the default.
3
Convert WEEKDAY numbers to three-letter day names, where WEEKDAY mode 2 returns 1 for Monday through 7 for Sunday.
| A | B | |
|---|---|---|
| 1 | Date | Day |
| 2 | 2024-01-15 | =SWITCH(WEEKDAY(A2,2),1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7,"Sun") |
| 3 | 2024-01-16 | Tue |
| 4 | 2024-01-17 | Wed |
| 5 | 2024-01-20 | Sat |
| 6 | 2024-01-21 | Sun |
Row 2: Mon
— January 15 2024 was a Monday. WEEKDAY with mode 2 returns 1 for Monday, and SWITCH maps 1 to Mon.
WEEKDAY mode 2 numbers days Monday=1 through Sunday=7, which maps cleanly to the SWITCH value list. Changing mode 1 (Sunday=1) would require reordering the SWITCH pairs.
Common use cases
1. Translate department codes stored in a database export into full department names for a report
=SWITCH(B2,"ENG","Engineering","MKT","Marketing","SLS","Sales","Unknown")
2. Convert numeric status codes from an API response to human-readable labels in a dashboard
=SWITCH(C2,1,"Active",2,"Suspended",3,"Closed","Unknown")
3. Map WEEKDAY numbers to day names for scheduling and calendar displays
=SWITCH(WEEKDAY(A2,2),1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7,"Sun")
4. Display a currency symbol based on a currency code column in a financial report
=SWITCH(D2,"USD","$","GBP","£","EUR","€","JPY","¥","?")
5. Convert country codes to country names in a contact database without a separate lookup table
=SWITCH(E2,"US","United States","GB","United Kingdom","DE","Germany","Unknown")
Common errors
#N/A for some rows
The expression value for those rows does not match any listed value and no default argument was provided.
Fix: Always add a final unpaired default argument after the last value-result pair. Unknown or Other are common choices. SWITCH returns #N/A when there is no match and no default.
Wrong result returned
The expression contains extra spaces or mixed casing. SWITCH uses exact matching so "ENG " with a trailing space does not match "ENG".
Fix: Use TRIM around the expression to strip extra spaces: =SWITCH(TRIM(B2),"ENG","Engineering",...). Text matching in SWITCH is case-insensitive so casing differences are not the issue.
#NAME? error on the SWITCH function itself
SWITCH is not available in Excel 2016 or earlier. Older versions do not recognise the function name.
Fix: Replace SWITCH with nested IF or IFS for Excel 2016 and earlier. If the file only needs to open in Excel 2019 or newer, SWITCH is safe.
Tips and variations
Always include a default argument for unrecognised values
SWITCH returns #N/A for any expression that does not match a listed value when no default is given. Add a final unpaired argument — Unknown, Other, or an empty string — so unexpected codes produce a safe fallback rather than an error.
=SWITCH(B2,"ENG","Engineering","MKT","Marketing","Unknown")
Use TRIM on the expression to handle extra spaces in source data
SWITCH uses exact matching, so a department code with a trailing space will not match the clean version in the formula. Wrap the expression in TRIM so spacing differences in source data do not silently break the mapping.
=SWITCH(TRIM(B2),"ENG","Engineering","MKT","Marketing","Unknown")
Combine SWITCH with WEEKDAY for clean calendar displays
Nesting WEEKDAY inside SWITCH is the cleanest way to convert date cells into day names without a helper table. Pick WEEKDAY mode 2 for Monday-to-Sunday ordering which maps directly to a sequential 1-7 list.
=SWITCH(WEEKDAY(A2,2),1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7,"Sun")
Excel vs Google Sheets
Excel vs Google Sheets
SWITCH is available in Excel 2019, Excel 365, and all versions of Google Sheets. It is not available in Excel 2016 or earlier. The syntax and exact-match behaviour are identical in Excel 2019+ and Google Sheets. Formulas copy between the two without changes.
Frequently asked questions
SWITCH compares a single expression against a list of values and returns the result paired with the first value that matches. If none match, it returns a default result you provide, or #N/A if no default is given. It is the cleaner alternative to IFS when you are mapping one expression to a set of fixed output values rather than evaluating range conditions.
IFS evaluates a series of independent conditions, each of which can be any logical test. SWITCH evaluates one expression once and compares it against a list of fixed values. Use IFS for range conditions like >=90 or <=5. Use SWITCH when you are translating specific codes or names into labels, like department codes to full names or status numbers to status text.
Add a final argument after the last value-result pair that is not paired with a match value — it stands alone and acts as the default. The pattern is =SWITCH(expression, value1, result1, value2, result2, ..., default). If no match is found and no default is given, SWITCH returns #N/A.
Not directly in a single pair, but you can list the same result multiple times for different values. In Excel 365, SWITCH supports grouping multiple match values to one result using a comma inside curly braces, but the standard syntax requires a separate pair for each value. IFS is usually simpler when multiple different conditions should produce the same output.
No. SWITCH was introduced in Excel 2019 and is available in Excel 365. It is not in Excel 2016 or earlier. For older Excel versions, use nested IF or IFS to replicate the same logic. SWITCH is available in all modern versions of Google Sheets.