Skip to content

Excel function · Logical

Excel SWITCH function

SWITCH compares the result of an expression with the values you list, in order, and returns the result of the first match; if nothing matches, it returns the default. Use it to turn payment codes into names and similar jobs.

In Turkish Excel: İLKEŞLEŞEN

What it does

The first argument is the expression to compare: a cell or a formula such as WEEKDAY(A2,2). Value-result pairs follow: "C","Cash","D","Debit card". Whichever value the expression equals, the result next to it comes back. If one argument is left over after the pairs, it is the default.

Because the expression is written once, it is much shorter than nested IFs that compare the same cell again and again. The comparison is equality only; for range tests such as >=, use IFS.

It is available in Microsoft 365, Excel 2019 and later. To choose by a position number, use CHOOSE; if the options form a long list that changes often, a mapping table with VLOOKUP or XLOOKUP fits better.

Syntax

English Excel=SWITCH(expression, value1, result1, ..., [default])
Turkish Excel=İLKEŞLEŞEN(ifade; değer1; sonuç1; ...; [varsayılan])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
expressionRequiredThe value or formula to compare, e.g. B2.
value1RequiredThe first value to compare expression with.
result1RequiredThe result returned if expression equals value1. Further pairs follow as value2, result2; up to 126 pairs are allowed.
defaultOptionalThe result returned when no value matches. If omitted, #N/A is returned.

Examples

1Turn a payment code into a name

The one-letter code in column B is compared with three values. For a code that matches none of them, the last argument, the default, is returned.

C2fx=SWITCH(B2,"C","Cash","D","Debit card","T","Bank transfer","Check code")
Turn a payment code into a name: example table
ABC
1OrderPayment CodePayment Type
2SO-701CCash
3SO-702DDebit card
4SO-703TBank transfer
5SO-704DDebit card
6SO-705XCheck code
  • C2 (filled down to C6)=SWITCH(B2,"C","Cash","D","Debit card","T","Bank transfer","Check code")

SO-702 shows Debit card and SO-703 shows Bank transfer. The "X" in SO-705 matches none of the listed values, so it gets Check code.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Overtime rate multiplier by day

Under the company rule, Saturday overtime is paid at 1.5 times and Sunday at 2 times. The expression is a WEEKDAY formula; days other than 6 and 7 get the default multiplier of 1. Column D works out the pay.

C2fx=SWITCH(WEEKDAY(A2,2),6,1.5,7,2,1)
Overtime rate multiplier by day: example table
ABCDEF
1DateHoursMultiplierPayHourly Rate
26-Nov-202631750.00250.00
37-Nov-202651.51,875.00
48-Nov-2026422,000.00
59-Nov-202621500.00
  • C2 (filled down to C5)=SWITCH(WEEKDAY(A2,2),6,1.5,7,2,1)
  • D2 (filled down to D5)=B2*$F$2*C2

The multiplier is 1.5 on Saturday and 2 on Sunday. Four hours on Sunday 8 November pay 2,000.00; three hours on Friday 6 November pay 750.00.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#N/A

Cause: The expression matched none of the values and no default was given.

Fix: Add a default as the last argument: ,"Check code".

Code not matched

Cause: The code in the cell has a trailing space, or it is stored as a number while the formula has it as text ("1" against 1).

Fix: Clean the codes with TRIM; write number codes without quotes in the formula.

#NAME?

Cause: The file was opened in a version without SWITCH (Excel 2016 or earlier).

Fix: Use nested IF or a mapping table with VLOOKUP.

Good to know

  • List values that share a result as separate pairs: SWITCH(WEEKDAY(A2,2),6,"Weekend",7,"Weekend","Weekday").
  • Text comparisons are not case-sensitive: "c" and "C" count as the same.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a column to the order list that turns the payment codes C, D and T into Cash, Debit card and Bank transfer, and writes "Check code" for unknown codes

Convert to table

Bring your order list into the tool and type this. Nexell adds the column with real Excel formulas and the preview shows any unknown codes; in the downloaded file, the description changes when the code does.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between SWITCH and IFS?

SWITCH compares one expression with values for equality and can take a default. IFS tests a separate condition in each pair; use it for range comparisons such as B2>=85.

Related functions

All logical functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free