SWITCH

What it does

SWITCH looks at one value and compares it with a list of options. When it finds an exact match, it returns the result paired with it. A last, unpaired argument is the default for when nothing matches.

Syntax

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

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

expression

The value to compare, usually a cell.

value

A value to compare it with.

result

What to return when that value matches.

default

What to return when nothing matches. Without it, no match gives #N/A.

Example

Size codes are S, M and L. What size is the code in A2?

The formula in B2:

=SWITCH(A2,"S","Small","M","Medium","L","Large","?")

Result: Medium

A2 holds M. It doesn't match S but it does match M, so the result paired with M is returned: Medium.

Common mistakes

• Expecting comparisons such as greater than. SWITCH only checks exact matches; for bands of values use IFS.
• Losing track of the pairs. Each value needs a result after it, and an odd extra argument at the end becomes the default.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• SWITCH: match a valueModule 4 · Logic

More from this module

• IF
• IFS
• AND
• OR
• NOT
• XOR
• ISNUMBER
• ISTEXT
• ISBLANK
• ISERROR
• IFERROR

All functions