SWITCH function in Excel

SWITCH compares one value with a list of possible values and returns the result that goes with the first match.

SWITCH turns one set of values into another: a code into a name, a region into an office, a status letter into a word. It does what a chain of IF(A2="x", ..., IF(A2="y", ... does, without repeating the cell reference in every step. It is available in Excel 2019, Excel 2021 and Microsoft 365.

Syntax

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

Argument	What it is
`expression`	The value to test, usually a cell.
`value1, result1`	A value to compare with, and what to return if it matches.
`value2, result2, ...`	More pairs.
`default`	What to return when nothing matches. It is the last argument and has no partner.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=SWITCH(C2,"North","N","South","S","East","E","?")
Result: N
The region in C2 is North, which matches the first pair: N.
=SWITCH(C5,"North","Delhi office","South","Chennai office","East","Kolkata office","No office")
Result: Kolkata office
C5 is East, so the third result is returned.
=SWITCH(D2,"Pen",5,"Notebook",12,18)
Result: 12
A GST-style rate by product: Notebook matches the second pair, 12. The lone 18 at the end is the default.
=SWITCH(D4,"Pen",5,"Notebook",12)
Result: #N/A
D4 is Bag. Nothing matches and there is no default, so the result is #N/A.
=SWITCH(WEEKDAY(A2),1,"Weekend",7,"Weekend","Working day")
Result: Working day
The expression can be a formula. The sale in A2 was on a Monday, which is neither 1 nor 7.
=SWITCH(TRUE,E2>=50,"Large",E2>=20,"Medium","Small")
Result: Medium
A trick: switching on TRUE turns each "value" into a condition. E2 is 20, so Medium.

How it works

SWITCH takes the expression, usually a single cell, and compares it with each value in turn. At the first exact match it returns the result paired with that value. If nothing matches, it returns the default, which is the last argument when the number of arguments after the expression is odd.

Compare the two ways of writing the same thing:

=IF(C2="North","N",IF(C2="South","S",IF(C2="East","E","?")))
=SWITCH(C2,"North","N","South","S","East","E","?")

The cell is named once, and there is one closing bracket.

Always give a default

Without a default, a value that is not on the list produces #N/A, as in the fourth example. Sometimes that is right: an unknown code should be noticed. More often a default such as "Other" or "Check" is kinder.

SWITCH matches exactly

SWITCH tests for equality only. It cannot say "greater than 50" in the ordinary way. For ranges of numbers use IFS, or the SWITCH(TRUE, ...) trick in the last example, where each "value" is a condition and the first one that equals TRUE wins. Many people find IFS clearer for that job.

Matching ignores capital and small letters, so "north" matches "North". It does not ignore spaces.

SWITCH, IFS, CHOOSE or a lookup?

You have	Use
one cell to compare with a few fixed values	SWITCH
different conditions, such as number ranges	IFS
a number 1, 2, 3 that picks from a list	CHOOSE
more than six or seven pairs, or pairs that change	a table and XLOOKUP or VLOOKUP

The last row matters. A formula with fifteen pairs typed into it is a small database hidden where nobody can see or edit it. Put the pairs in two columns on a sheet and look them up.

Common mistakes

#NAME? in Excel 2016 or earlier, which do not have SWITCH.
#N/A because no value matched and no default was given.
A default placed in the middle. It must be the final argument.
Numbers typed as text. SWITCH(A2,"1","One") does not match a cell that holds the number 1. Leave the quotation marks off numbers.

In Google Sheets

SWITCH is available in Google Sheets with the same arguments.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.