SWITCH function

SWITCH evaluates an expression and returns the matching result value, or a default if provided, simplifying multi-branch logic in a single formula.

=SWITCH(expression, value1, result1, ...)

Generate a SWITCH formula

Describe what you need. The generator will reach for SWITCH where SWITCH is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SWITCH reads its arguments
expressionrequiredvalue1requiredresult1requiredSWITCH →
ArgumentRequiredDescription
expressionRequiredAny scalar value or expression; if it evaluates to an error, SWITCH propagates that error.
value1RequiredThe first value to compare against the expression; must be a scalar of the same type as the expression for a proper match.
result1RequiredThe result returned when expression equals value1; can be any type, including another formula.
...RepeatingOptional repeating pairs of value/result; each value is compared in order, and the first exact match returns its associated result. If the final argument is unpaired, it is treated as the default result.

Returns

SWITCH returns a single scalar value of any type (text, number, date, or error).

Availability

Excel: 2019+ · Google Sheets: Supported

Worked examples

1. Classify MRR into revenue tiers

CustomerPlanMRRSignup DateChurn Date
Acme CorpPro12002023-01-15
Beta LLCBasic3002023-03-022024-02-20
Gamma IncEnterprise25002022-11-10
=SWITCH(TRUE, C2<500,"Low", C2<1000,"Medium", C2>=1000,"High")

Result: High

The expression TRUE forces SWITCH to treat each subsequent value as a logical test. C2 (the MRR for Acme Corp) is 1200, which fails the first two tests (<500 and <1000) but satisfies C2>=1000, so the function returns "High".

2. Map plan name to annual discount percentage

CustomerPlanMRRSignup DateChurn Date
Acme CorpPro12002023-01-15
Beta LLCBasic3002023-03-022024-02-20
Delta CoPremium8002023-07-01
=SWITCH(B2,"Basic",0,"Pro",10,"Enterprise",20,"Unknown")

Result: 10

B2 contains the plan name "Pro". SWITCH compares it sequentially to the listed values. It matches the second value "Pro" and returns the associated result 10. Because a match is found, the final default "Unknown" is ignored.

3. Show subscription status based on churn date

CustomerPlanMRRSignup DateChurn Date
Acme CorpPro12002023-01-15
Beta LLCBasic3002023-03-022024-02-20
Epsilon LtdEnterprise18002022-05-20
=SWITCH(D2,"","Active","Churned")

Result: Active

D2 is blank for Acme Corp, meaning the subscription is still active. SWITCH checks the first value (empty string) and finds a match, so it returns "Active". If D2 contained any date, the function would fall through to the default result "Churned".

Common errors

Which SWITCH error are you seeing?
SWITCH returned an error#N/A
Add a final unpaired argument to serve as a default, e.g., =SWITCH(A2,1,"One",2,"Two","Other").
#VALUE!
Ensure every value argument is followed by its result; if you need a default, place it as the last argument without a preceding value.
#REF!
Correct the cell reference or replace it with a valid constant; for example, change =SWITCH(Z99,…) to a reference that exists.
ErrorWhy it happensHow to fix it
#N/ANo value/result pair matches the expression and no default result is supplied.Add a final unpaired argument to serve as a default, e.g., =SWITCH(A2,1,"One",2,"Two","Other").
#VALUE!The arguments are not supplied in value/result pairs, leaving a value without a corresponding result.Ensure every value argument is followed by its result; if you need a default, place it as the last argument without a preceding value.
#REF!The expression or one of the value arguments references a cell that has been deleted or is otherwise invalid.Correct the cell reference or replace it with a valid constant; for example, change =SWITCH(Z99,…) to a reference that exists.

Tips and when to use something else

  • Place the default result as the last argument to avoid #N/A when no match is found.
  • Use SWITCH with TRUE as the expression to evaluate range-based conditions, similar to nested IFs.
  • When you have many mutually exclusive conditions based on a numeric index, CHOOSE can be more concise.
  • If you need overlapping conditions or want the first true condition without a default, consider using IFS instead of SWITCH.

Related functions

IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.IFSIFS evaluates multiple logical tests in order and returns the first matching value, letting you replace nested IFs with a single readable formula.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.CHOOSECHOOSE returns the value or range from a list of arguments that corresponds to the supplied index number, letting you pick one of several options.

Frequently asked questions

Can SWITCH handle text and numbers in the same formula?
Yes. SWITCH compares the expression to each value exactly, regardless of type. If you mix types, ensure the expression and each value are comparable; otherwise, a mismatch will result in no match and the default (or #N/A).
How does SWITCH differ from nested IF statements?
SWITCH evaluates one expression against multiple possible values, making the formula easier to read and maintain. Nested IFs evaluate separate logical tests, which can become hard to follow when many branches exist.
Is there a limit to the number of value/result pairs in SWITCH?
Excel allows up to 254 arguments, which translates to 127 value/result pairs plus an optional default. Practically, very long SWITCH formulas become unwieldy, and a lookup table with VLOOKUP or XLOOKUP is often preferable.
Why does SWITCH return #N/A even though I think a match exists?
SWITCH requires an exact match; it does not perform partial or case-insensitive matching. Ensure the expression and the value strings have identical characters, or use functions like LOWER() to normalize before the SWITCH call.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator