SWITCH Function

Summary

The Excel SWITCH function compares one expression against a list of values and returns the result for the first match. It is especially helpful when you want to map known codes, labels, or categories to clearer outputs.

One reason SWITCH is easier to read than some alternatives is that the expression only appears once. After that, you list the possible matches and the result for each one. This can make code-to-label formulas much cleaner than a long chain of IF statements.

SWITCH is most useful when one input value should map to one of several fixed outputs. That makes it easier to read than repeated IF checks when the workbook is really doing a direct value-to-result lookup inside the formula.

Syntax

=SWITCH(expression, value1, result1, [value2, result2], ..., [default])

The first argument is the expression Excel will compare. Each following pair contains a value to match and the result to return if it matches. Microsoft documents support for up to 126 value/result pairs, with an optional default as the final argument.

Arguments

• expression - [required] The value or formula to compare.
• value1, value2 ... - Values to test against the expression.
• result1, result2 ... - The result to return when the corresponding value matches.
• default - [optional] The result to return when no listed value matches.

If no match is found and no default is supplied, SWITCH returns #N/A.

SWITCH vs IFS

SWITCH and IFS can sometimes solve the same problem, but they approach it differently.

If your logic is based on exact known values, SWITCH is usually the more natural fit. If your logic is based on ranges or comparisons, IFS is often easier to read.

Using SWITCH

A simple SWITCH formula can replace a chain of repeated comparisons. For example, instead of checking the same cell again and again with IF, you can write the expression once and then list the outcomes. That keeps short mapping formulas tidy and easier to maintain.

It is also a good idea to include a default result whenever the input might contain unexpected values. A default such as "Unknown" or 0 helps prevent a raw #N/A result and gives the reader a clearer outcome.

You may also see the pattern SWITCH(TRUE,...). In that setup, the expression is the logical value TRUE, and each comparison is written as a condition such as B2>90. It works because Excel compares TRUE to the first condition that also evaluates to TRUE. This is a useful trick, though for many range-based formulas IFS may still read more naturally.

Example 1 - Map Numeric Codes

This is a straightforward exact-match example.

=SWITCH(B2,1,"Low",2,"Med",3,"High")

If B2 is 2, the result is "Med". This is a good fit for status codes, small category lists, and simple internal labels.

Example 2 - Add a Default Label

A default keeps the formula from returning an unhelpful error when no match is found.

=SWITCH(B2,"A","Admin","U","User","Guest")

If B2 is "A", the result is "Admin". If it is "U", the result is "User". If it is something else, the formula returns "Guest".

Example 3 - Use SWITCH(TRUE,...) for a Range Test

This pattern turns SWITCH into an ordered logical test.

=SWITCH(TRUE(),B2>10,"Big","Small")

If B2 is above 10, the comparison B2>10 evaluates to TRUE, so the formula returns "Big". Otherwise it falls through to the default result "Small".

Example 4 - Return Values from Other Cells

SWITCH can also route between different cell values.

=SWITCH(B2,1,G2,2,H2,0)

If B2 is 1, the formula returns the value in G2. If B2 is 2, it returns H2. Otherwise it returns 0. This makes SWITCH useful for simple selection-based models.

Conclusion Recap

SWITCH is a clean way to map one value to one result. In this lesson, it handled code labels, default outputs, range-style logic with SWITCH(TRUE,...), and returning values from other cells.

It works best when you already know the exact options you want to match. That is what makes it easier to read than a long chain of repeated IF tests.

• Core idea: SWITCH compares one expression with a list of possible values.
• Best use: Exact-match mapping such as codes, labels, and small controlled lists.
• Cleaner structure: The expression appears once instead of being repeated in many IF tests.
• Default option: Add a final fallback value to avoid #N/A when nothing matches.
• Useful trick: SWITCH(TRUE,...) can handle ordered conditions.
• When to choose IFS: If your logic is mainly about ranges and thresholds, IFS may be easier to understand.