Excel SWITCH Function: Syntax, Examples and Tips

Q: How many value/result pairs can SWITCH handle?

Up to 126 pairs plus a default. For long code lists, a two-column table with XLOOKUP or VLOOKUP is easier to maintain.

Q: What happens if no value in SWITCH matches?

SWITCH returns #N/A unless you supply a default as the final argument, in which case the default is returned.

Q: What is the difference between SWITCH and IFS?

SWITCH tests one expression for equality against several values. IFS evaluates independent logical tests, so it handles ranges like B2>=90 and mixed conditions that SWITCH cannot.

Part of the free Module 5: Excel Formulas and Functions · Function 82 of 105 · Full Excel course

The Excel SWITCH function compares one expression against a list of values and returns the result paired with the first exact match, or an optional default when nothing matches. SWITCH in Excel replaces a chain of nested IF(A2=”x”, …, IF(A2=”y”, …)) tests with a single readable list, ideal for translating codes into names or categories.

Table of Contents

SWITCH syntax

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

Arguments

Argument	Required	Meaning
expression	Required	The value to test, evaluated once: a cell, text, number or formula.
value1, result1	Required	The first value to compare against expression and the result to return when they are equal.
value2, result2 …	Optional	Additional pairs, up to 126.
default	Optional	Returned when no value matches. Supplied as a final single argument with no pairing value.

SWITCH is available in Excel 2019, 2021, 365 and Excel for the web. Text comparison is not case-sensitive.

Step-by-step example

Translate a department code in A2 into its full name:

=SWITCH(A2, "HR", "Human Resources", "FIN", "Finance", "IT", "Information Technology", "Unknown Department")

Excel evaluates A2 once, say “FIN”.
It compares “FIN” with “HR” (no), then “FIN” (yes).
It returns “Finance” and stops. If A2 held “OPS”, nothing would match and the default “Unknown Department” would be returned.

Practical use cases

1. Rating number to label

=SWITCH(A2, 1, "Poor", 2, "Fair", 3, "Good", 4, "Excellent", "Not Rated")

2. Price by product with a calculation as the result

=SWITCH(B2, "ProductA", C2*10, "ProductB", C2*15, "ProductC", C2*20, "Product not found")

3. Weekday name from a date

=SWITCH(WEEKDAY(A2), 1, "Sun", 2, "Mon", 3, "Tue", 4, "Wed", 5, "Thu", 6, "Fri", 7, "Sat")

4. Feedback text to score

=SWITCH(E2, "Very Satisfied", 5, "Satisfied", 4, "Neutral", 3, "Dissatisfied", 2, "Very Dissatisfied", 1, 0)

5. Range conditions: use TRUE as the expression

=SWITCH(TRUE, B2>=90, "A", B2>=80, "B", B2>=70, "C", "D")

Because each comparison returns TRUE or FALSE, comparing against TRUE turns SWITCH into an IFS-style banding formula. IFS itself is usually clearer for this job.

Common mistakes and errors

#N/A – no value matched and no default was given. Add a final default argument.
Trying to use > or < in the value list – SWITCH only tests equality. Use IFS, or the SWITCH(TRUE, …) pattern above.
Wildcards not supported – “Prod*” is treated as literal text. Use IFS with ISNUMBER(SEARCH(…)) for partial matches.
#NAME? – the file is open in Excel 2016 or earlier without SWITCH. Use nested IF or CHOOSE with MATCH for compatibility.
Odd argument count confusion – the default is recognised only because it has no partner; make sure every other value has a result.

Tips and best practices

Always supply a default so unexpected codes show a clear label instead of #N/A.
Keep lists under about ten pairs; beyond that a two-column mapping table with XLOOKUP is easier to maintain.
Use SWITCH(TRUE, …) only for short banding and prefer IFS when readers may not know the trick.
Put the expression in a cell and reference it, so SWITCH evaluates a simple value rather than a long formula.
Verify the Excel version of everyone who opens the file; SWITCH needs Excel 2019 or later.

Related functions

IFS – for range and compound conditions.
CHOOSE – selects by position number instead of by value.
IF – the single-condition original.
VLOOKUP – a lookup table scales better than a long SWITCH list.
Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.

Frequently asked questions

How many value/result pairs can SWITCH handle?

Up to 126 pairs plus a default. For long code lists, a two-column table with XLOOKUP or VLOOKUP is easier to maintain.

What happens if no value in SWITCH matches?

SWITCH returns #N/A unless you supply a default as the final argument, in which case the default is returned.

What is the difference between SWITCH and IFS?

SWITCH tests one expression for equality against several values. IFS evaluates independent logical tests, so it handles ranges like B2>=90 and mixed conditions that SWITCH cannot.

Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.