Choosing Cases With SWITCH

When you are comparing one value against a list of exact possibilities, nested IFs get repetitive. You keep typing the same cell reference over and over.

The SWITCH function solves this. You name the value once, then list each possible match alongside the result it should produce. It reads like a clean lookup table written inside a single formula.

The structure is: SWITCH(expression, value1, result1, value2, result2, ..., default).

• expression is the thing you are evaluating, usually a cell.
• Then you pair each possible value with its result.
• An optional default at the end catches anything that did not match.

The values come in pairs, with a lone default allowed at the very end.

Imagine a day number 1 through 7 in cell A2 and you want the weekday name. SWITCH maps each number to a label.

SWITCH compares the expression to each value using exact equality, so 1 matches only 1, never "close to 1".

What if cell A2 holds a value you never listed, like 8 or a typo? Without a default, SWITCH returns the #N/A error.

Add one final argument with no matching value in front of it. SWITCH treats a lone trailing argument as the default to use when nothing matched.

This is the most important rule: SWITCH only checks for exact equality. It cannot do "greater than" or "between" tests.

So SWITCH is perfect for codes, IDs, statuses, and category names. It is the wrong choice for grading by score ranges. For ranges, you still need nested IFs or IFS with comparison tests.

Compare the same logic both ways. With nested IF you repeat A2 three times:

=IF(A2="N","North",IF(A2="S","South",IF(A2="E","East","West")))

With SWITCH you name A2 just once and the rest is clean pairs. Fewer parentheses, less repetition, and far easier to read or edit later.

Just like IF, the results in SWITCH can be numbers or even calculations, not only text labels.

Here a shipping code drives a flat fee. A code of STD returns 5, EXP returns 15, and anything else falls to the default fee of 25.

There is a way to make SWITCH handle ranges after all. Set the expression to TRUE, then make each value a full comparison. SWITCH returns the result of the first comparison that equals TRUE.

This mimics a nested IF range test while keeping the tidy SWITCH layout. Remember to order the comparisons from highest to lowest.

A status code in A2 should become a friendly message and a follow-up. We map four codes and provide a safety default:

• 1 means "New order"
• 2 means "Shipped"
• 3 means "Delivered"
• 4 means "Cancelled"
• anything else means "Check record"

SWITCH is available in Excel 2019, Excel for Microsoft 365, and Google Sheets. It is not in very old versions like Excel 2013 or earlier.

If you ever open a workbook in an old version, SWITCH may appear as #NAME?. In that case, fall back to nested IFs, which work everywhere.

Get the most from SWITCH by remembering:

• Use it for exact matches: codes, statuses, categories.
• Always add a default to avoid #N/A surprises.
• Keep the value-result pairs lined up so they are easy to scan.
• For range logic, use the SWITCH(TRUE, ...) trick or stick with IFS.

Test your understanding of how SWITCH compares values.

You learned to map one value to many outcomes with SWITCH. Key points:

• Syntax is SWITCH(expression, value1, result1, ..., default).
• It matches by exact equality, perfect for codes and statuses.
• Always include a default to avoid #N/A.
• Use SWITCH(TRUE, comparison, result, ...) when you need range logic.