The SWITCH Function in Excel (Turn Codes Into Labels)

The Excel SWITCH function turns short codes into readable labels. You give it one value, such as a status code, and a list of possible matches with their results. It returns the result for the first exact match, or a default if nothing matches. This guide covers the syntax, the default value, and when IFS or a lookup table is the better tool.

How does the Excel SWITCH function work?#

SWITCH compares one value, the expression, with each option in turn. When an option matches exactly, it returns the result paired with it:

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

You can list up to 126 pairs. SWITCH works in Excel 2019, Excel 2021, Excel 2024, Microsoft 365 and Excel for the web.

How to turn codes into labels with SWITCH#

An order export often stores status as one letter. SWITCH translates each code:

=SWITCH(D2,"P","Pending","S","Shipped","D","Delivered")

Text codes and labels both need double quotes. Numbers don't: =SWITCH(B2,1,"High",2,"Medium",3,"Low") works on numeric codes.

Look at the last row. R isn't in the list, so SWITCH has nothing to return and shows #N/A.

To build the formula yourself:

1. Click the first empty cell of the Status column and type =SWITCH(.
2. Click the code cell, D2, and type a comma.
3. Type each code and its label as a pair, with commas between them: "P","Pending","S","Shipped",.
4. Finish the last pair, close the bracket and press Enter.
5. Fill down with Ctrl+D (Windows) or ⌘D (Mac). D2 changes to D3, D4 and so on.

Check the pairs before you fill down. If a code and its label are the wrong way round, SWITCH won't warn you; it just never finds a match.

How to add a default value#

Add one extra value after the last pair, with no option in front of it. SWITCH shows it whenever nothing matches:

=SWITCH(D2,"P","Pending","S","Shipped","D","Delivered","Check code")

Now the R row shows "Check code" instead of #N/A. A default is good practice in almost every SWITCH formula, because new codes and typos turn up in real data. Make the default say something useful, so readers know the row needs a look.

Don't use IFERROR to hide the #N/A instead. A default handles the "no match" case on purpose, while IFERROR would also hide other problems. See IFERROR, used the right way for when wrapping makes sense.

How does SWITCH compare with IF and IFS?#

Use SWITCH when you check one cell against exact values. Use IFS or nested IF when the tests are ranges or different conditions. Here is the same code lookup written three ways:

=IF(D2="P","Pending",IF(D2="S","Shipped",IF(D2="D","Delivered","Check code")))
=IFS(D2="P","Pending",D2="S","Shipped",D2="D","Delivered",TRUE,"Check code")
=SWITCH(D2,"P","Pending","S","Shipped","D","Delivered","Check code")

SWITCH is the shortest because the cell appears once. For grades, price bands and other ranges, read the nested IF guide for grades and bands or the IFS function guide. If plain IF is still new, start with the Excel IF function explained simply.

Can SWITCH handle ranges?#

There is a well-known workaround: switch on TRUE, and use tests as the options.

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

SWITCH returns the result for the first test that equals TRUE. It works, but IFS says the same thing more plainly, so most people keep SWITCH for exact codes.

How to use SWITCH with numbers and other functions#

The expression can be a formula, not just a cell. A common example names days of the week from WEEKDAY, which returns 1 for Sunday through 7 for Saturday:

=SWITCH(WEEKDAY(B2),1,"Sunday",7,"Saturday","Weekday")

For a date that falls on a Saturday, this gives "Saturday". For Monday to Friday it gives "Weekday", thanks to the default. Excel works out WEEKDAY once, then compares the result with each option.

When should you use a lookup table instead?#

Use a lookup table when the list is long or changes often. Ten or more codes in one formula are hard to read, and every new code means editing every copy of the formula.

Put the codes in one column and the labels in the next, then look them up:

=XLOOKUP(D2,$K$2:$K$12,$L$2:$L$12,"Check code")

XLOOKUP needs Excel 2021 or Microsoft 365. The fourth argument does the same job as SWITCH's default. When a new code appears, you add a row to the table and every formula picks it up.

What are the common SWITCH mistakes?#

The last two data problems are easy to miss, because the cell looks right. Check suspicious cells with ISNUMBER, ISTEXT and ISBLANK, or use LEN to count the characters.

Practise it#

Try the priority exercise on this page, then practise SWITCH and the IS functions in a live spreadsheet. You'll check messy imported data with ISBLANK and ISNUMBER, then turn status codes into labels with SWITCH and add a default for a new code.