SWITCH Function in Excel, Google Sheets, Airtable & Notion
SWITCH compares one value against a list of options and returns the result paired with the first exact match, or a default if nothing matches.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | SWITCH(expression, value1, result1, [value2, result2], ..., [default])Excel 2019 / 365 |
| Google Sheets | ✔ Built in | SWITCH(expression, case1, value1, [case2, value2, ...], [default]) |
| Airtable | ✔ Built in | SWITCH(expression, pattern, result, ..., [default]) |
| Notion | ≈ Workaround | ifs(x == "a", result1, x == "b", result2, ..., default)Notion has no switch(); compare the value in each condition of ifs(). Text comparison with == is case-sensitive. |
| Excel | =SWITCH("M", "S", "Small", "M", "Medium", "L", "Large", "Unknown") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =SWITCH("M", "S", "Small", "M", "Medium", "L", "Large", "Unknown") | open sheets.new ↗ |
| Airtable | SWITCH("M", "S", "Small", "M", "Medium", "L", "Large", "Unknown") | |
| Notion workaround | ifs("M" == "S", "Small", "M" == "M", "Medium", "M" == "L", "Large", "Unknown") |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Excel and Google Sheets compare text without regard to case. The Notion version uses ==, which is case-sensitive, and returns empty text instead of an error when nothing matches.
Arguments
| Argument | What it means |
|---|---|
expression | The value to check, usually a cell such as A2. |
value1, result1 | If expression equals value1, return result1. |
[value2, result2], ... optional | More pairs, checked in order. |
[default] optional | Returned when nothing matches. Without it, Excel and Google Sheets return #N/A. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=SWITCH("M", "S", "Small", "M", "Medium", "L", "Large", "Unknown") | Medium | ||
=SWITCH("XL", "S", "Small", "M", "Medium") | #N/A | No match and no default. | |
=SWITCH(WEEKDAY(A2), 1, "Weekend", 7, "Weekend", "Weekday") | "Weekend" or "Weekday" | Map numbers to labels. |
Examples use Excel / Google Sheets syntax unless noted.
How SWITCH works
SWITCH is the tidy way to translate codes into labels: status codes into names, region abbreviations into full names, size letters into descriptions. It only does exact matches. For ranges such as "80 and above", use IFS instead; for long or frequently changing lists, keep a two-column table and use XLOOKUP or VLOOKUP so you can edit the mapping without touching the formula.
Arguments come in pairs after the expression. If the total number of arguments after the expression is odd, the last one is the default. Forgetting the default means unmatched values show #N/A in Excel and Google Sheets. Airtable's SWITCH() has the same shape and is the usual way to map single-select options to other values.
Notion has no switch(), so the copy button writes the equivalent ifs(): each condition compares the value with ==, and the final argument is the default. With a property it reads ifs(prop("Size") == "S", "Small", prop("Size") == "M", "Medium", "Unknown").