Excel’s SWITCH function compares one expression with several possible values and returns the result attached to the first match. It is especially useful for converting codes into labels, assigning categories, and replacing long chains of nested IF formulas.
=SWITCH(A2,"P","Pending","C","Complete","Unknown")If A2 contains C, Excel returns Complete. If it contains anything else, it returns Unknown.
As an Amazon Associate I earn from qualifying purchases.
What is the Excel SWITCH function?
SWITCH evaluates one expression, compares it with a list of candidate values, and returns the result paired with the first matching value. You can also provide a default result for values that do not match.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →That makes it a good fit for exact-value mapping:
- Status codes such as
N,IP, andC - Department, region, or product codes
- Numeric categories such as weekday numbers
- Short lists of business categories
For example:
=SWITCH(A2,
"P","Pending",
"C","Complete",
"H","On hold",
"Unknown")This is easier to audit than the equivalent nested formula:
#1 Best Overall
- Desktop-Level Performance, Anywhere: Get legendary gaming performance with the Intel Core Ultra 9 275HX processor, delivering ultra-smooth gameplay and future-ready AI (Up to 13 NPU TOPS). Offload tasks like background removal and audio optimization to the NPU for seamless streaming and gaming, while Intel Application Optimization enhances performance on classic titles.
- Game-Changing Realism: Powered by NVIDIA Blackwell architecture, GeForce RTX 5070 Ti Laptop GPU unlocks the game changing realism of full ray tracing. Equipped with a massive level of 992 AI TOPS horsepower, the RTX 50 Series enables new experiences and next-level graphics fidelity. Experience cinematic quality visuals at unprecedented speed with fourth-gen RT Cores and breakthrough neural rendering technologies accelerated with fifth-gen Tensor Cores.
- Supreme Speed. Superior Visuals. Powered by AI: DLSS is a revolutionary suite of neural rendering technologies that uses AI to boost FPS, reduce latency, and improve image quality. DLSS 4 brings a new Multi Frame Generation and enhanced Ray Reconstruction and Super Resolution, powered by GeForce RTX 50 Series GPUs and fifth-generation Tensor Cores.
- The Ultimate in Ray Tracing and AI: NVIDIA RTX is the most advanced platform for full ray tracing and neural rendering technologies that are revolutionizing the ways we play and create. Over 700 games and applications use RTX to deliver realistic graphics and incredibly fast performance with cutting-edge AI features like DLSS Multi Frame Generation.
- Immersive Depth and Detail: At 18 inches with a 16:10 aspect ratio, the pristine WQXGA screen offering vibrant colors with up to 100% DCI-P3 operates at a fast 240Hz refresh and 3ms overdrive response time. Alongside the suite of features from NVIDIA G-SYNC and NVIDIA Advanced Optimus, you're guaranteed that whatever's on-screen is a distinct viewing delight.
=IF(A2="P","Pending",IF(A2="C","Complete",IF(A2="H","On hold","Unknown")))In ordinary use, SWITCH compares values for equality. It is not a general-purpose range, wildcard, or substring-matching function.
Microsoft documents the Excel worksheet function here: SWITCH function.
SWITCH syntax
=SWITCH(expression, value1, result1, value2, result2, default)A multiline version is often easier to read:
=SWITCH(
expression,
value1, result1,
value2, result2,
value3, result3,
default
)| Argument | Meaning |
|---|---|
expression |
The value or calculation Excel evaluates. |
value1, value2 |
The possible values to compare with the expression. |
result1, result2 |
The value, text, cell reference, or calculation returned for a match. |
default |
An optional final result returned when no candidate value matches. |
Every candidate value must have a corresponding result. The default, if included, is the final argument and does not have a matching value.
Microsoft states that SWITCH supports up to 126 value/result pairs, within Excel’s limit of 254 total arguments. If there is no match and no default is supplied, the result is #N/A.
How to enter a SWITCH formula
- Enter the input value in a worksheet cell, such as
A2. - Select the cell where the result should appear.
- Type
=SWITCH(. - Enter the input cell or expression.
- Add each value/result pair, separating arguments with commas.
- Add a default result if unmatched values are possible.
- Close the parenthesis and press Enter.
- Copy or fill the formula down if other rows use the same structure.
For a minimal working example, enter 1, 2, or 3 in A2 and use:
=SWITCH(A2,1,"Low",2,"Medium",3,"High","Invalid score")The formula returns Low, Medium, or High for those three values and Invalid score for anything else.
These examples use commas, as in many English-language Excel installations. If Excel expects semicolons in your region, replace the commas with semicolons.
Recommended Free Tools
Five Excel SWITCH examples
1. Convert weekday numbers into weekday names
Suppose A2 contains a number from 1 through 7, with 1 representing Sunday:
=SWITCH(A2,
1,"Sunday",
2,"Monday",
3,"Tuesday",
4,"Wednesday",
5,"Thursday",
6,"Friday",
7,"Saturday",
"Invalid day")| Value in A2 | Result |
|---|---|
| 1 | Sunday |
| 2 | Monday |
| 7 | Saturday |
| Other value | Invalid day |
You can also calculate the weekday number directly from a date:
=SWITCH(WEEKDAY(A2),
1,"Sunday",
2,"Monday",
3,"Tuesday",
4,"Wednesday",
5,"Thursday",
6,"Friday",
7,"Saturday",
"Invalid date")Here, SWITCH compares the result of WEEKDAY(A2), rather than comparing the cell contents directly. Microsoft shows a similar WEEKDAY/SWITCH pattern in its documentation.
2. Translate status codes into readable labels
Suppose A2 contains an internal project status code:
=SWITCH(A2,
"N","Not started",
"IP","In progress",
"C","Complete",
"H","On hold",
"Unknown status")| Code | Returned label |
|---|---|
N |
Not started |
IP |
In progress |
C |
Complete |
H |
On hold |
| Anything else | Unknown status |
This is one of the clearest uses for SWITCH: turning compact codes into labels that are easier to understand in a report.
If imported data may contain ordinary leading or trailing spaces, normalize the expression with TRIM:
=SWITCH(TRIM(A2),
"N","Not started",
"IP","In progress",
"C","Complete",
"H","On hold",
"Unknown status")TRIM does not resolve every data-cleaning problem. Nonbreaking spaces or inconsistent imported characters may require additional cleanup.
3. Return calculations instead of labels
The result arguments do not have to be text. They can be numbers, cell references, or formulas. Suppose A2 contains a region and B2 contains sales:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=SWITCH(A2,
"East",B2*1.05,
"West",B2*1.08,
"North",B2*1.03,
"South",B2*1.06,
B2)This applies an illustrative region-specific adjustment:
| Region | Calculation |
|---|---|
| East | B2*1.05 |
| West | B2*1.08 |
| North | B2*1.03 |
| South | B2*1.06 |
| Unrecognized region | B2 |
The percentages are only example assumptions; they are not general rules about regional sales. The important point is that each result can contain a different calculation.
4. Use SWITCH(TRUE,...) for ranges
Ordinary SWITCH is designed for exact comparisons. A common pattern for ranges is to make the expression TRUE and then provide conditions that evaluate to either TRUE or FALSE:
Rank #3
- Intel Core i9 HX Power for Elite Gaming: Dominate demanding titles with the Intel Core i9-14900HX and its 24-core hybrid architecture, delivering fast load times, high FPS, and smooth multitasking.
- GeForce RTX 5070 With Ray Tracing & DLSS 4: Powered by NVIDIA Blackwell, the RTX 5070 delivers stronger ray tracing, higher FPS, faster AI upscaling, and more responsive gameplay—ideal for competitive and cinematic gaming.
- QHD 165Hz, 100% DCI-P3 for Ultra-Clear Combat: The QHD 165Hz display reveals more detail, reduces motion blur, and boosts visibility in fast-paced games while delivering richer, more accurate colors.
- Cooler Boost 5 for Sustained Performance: Dual fans and a 5-heat-pipe share-pipe design keep the CPU and GPU cool, maintaining stable frame rates during long gaming marathons.
- 4-Zone RGB Keyboard + Full Game-Ready Ports: Customize your setup with a 4-zone RGB keyboard and highlighted WASD keys. Includes USB-C Gen 2, HDMI up to 8K, multiple USB-A ports, RJ45, Wi-Fi 6E & Hi-Res Audio.
=SWITCH(TRUE,
A2>=90,"A",
A2>=80,"B",
A2>=70,"C",
A2>=60,"D",
"F")| Score | Grade |
|---|---|
| 95 | A |
| 84 | B |
| 72 | C |
| 61 | D |
| 45 | F |
For a score of 84, A2>=90 is false, while A2>=80 is true. That condition matches TRUE, so Excel returns B.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Order matters. Put the highest or most restrictive threshold first:
=SWITCH(TRUE,
A2>=90,"A",
A2>=80,"B",
A2>=70,"C",
"F")This ordering is usually wrong:
=SWITCH(TRUE,
A2>=70,"C",
A2>=80,"B",
A2>=90,"A",
"F")A score of 95 satisfies A2>=70 first and would incorrectly return C.
SWITCH(TRUE,...) is a compact pattern, not a separate function mode. For condition-based logic, IFS may communicate the intent more directly:
=IFS(
A2>=90,"A",
A2>=80,"B",
A2>=70,"C",
A2>=60,"D",
TRUE,"F")5. Use a default for unexpected or missing categories
A fallback is valuable when users type values manually, data arrives from another system, or new categories may be added later:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitches=SWITCH(A2,
"Basic","Standard support",
"Pro","Priority support",
"Enterprise","Dedicated support",
"Contact administrator")For example, an unrecognized plan returns Contact administrator instead of an error.
You can handle a blank separately when that distinction matters:
=SWITCH(A2,
"","Missing",
"Basic","Standard support",
"Pro","Priority support",
"Enterprise","Dedicated support",
"Unknown plan")Whether a blank should be called Missing, left blank, or treated as an ordinary unknown value depends on the workbook’s data rules.
What happens when there is no match?
If you omit the default and Excel cannot find a matching value, SWITCH returns #N/A:
Rank #4
- Vibrant 15.6" FHD IPS Display: Experience stunning visuals on a large 15.6-inch Full HD (1920x1080) IPS screen. With narrow bezels and wide viewing angles, this laptop offers an immersive experience for streaming movies, online classes, or working on documents with crystal-clear detail
- Efficient Daily Performance: Powered by the Intel Celeron N4020 processor and 4GB LPDDR4 RAM, this notebook delivers reliable performance for web browsing, light multitasking, and school projects. The 128GB storage provides ample space for your essential files, photos, and apps
- Modern Connectivity & PD Fast Charge: Equipped with a versatile Type-C PD 45W port for fast charging and high-speed data transfer. Combined with Dual-Band AC WiFi and Bluetooth, you’ll enjoy a stable and fast internet connection for seamless video calls and cloud-based work
- Silent & Ultra-Portable Design: Featuring an advanced fanless cooling system, this laptop operates in total silence—perfect for libraries or late-night study sessions. Its sleek, lightweight body fits easily into backpacks, making it the ideal companion for students and commuters
- Ready for Work & Play: Pre-installed with Windows 11 Home, offering a secure and user-friendly interface. Includes a HD webcam and high-quality speakers for clear communication. A practical choice for online learning, remote work, or everyday entertainment
=SWITCH(A2,"Yes",1,"No",0)This formula works for Yes and No, but any other input—including an unexpected code or potentially a blank—has no matching result.
A safer operational version adds a fallback:
=SWITCH(A2,"Yes",1,"No",0,"Invalid response")Use a default when the result will appear in a report or when the input may change. A visible fallback can also help identify data-quality problems instead of silently hiding them.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.SWITCH versus IF, IFS, CHOOSE, and XLOOKUP
| Need | Best starting point | Why |
|---|---|---|
| One true/false test | IF |
Designed to return one result when a condition is true and another when it is false. |
| Several ordered conditions | IFS |
Checks conditions and returns the result for the first condition that is true. |
| One expression mapped to explicit values | SWITCH |
Clear and compact for short exact-value mappings. |
| Select by numeric position | CHOOSE |
Selects an item from a list using a numeric index. |
| Mapping stored in worksheet data | XLOOKUP |
Keeps the mapping in a separate, editable table. |
SWITCH versus nested IF
Choose SWITCH when the logic is “if this one expression equals one of these named values.”
=SWITCH(A2,"Open","Active","Closed","Finished","Unknown")Choose IF when the logic is a condition or comparison:
Free tools Windows power users keep installed
One-click scans. No signup required.
=IF(B2>C2,"Over budget","Within budget")Nested IF can handle multiple branches, but a long chain may be harder to read and audit.
SWITCH versus IFS
SWITCH is naturally suited to exact-value mapping. IFS is naturally suited to several conditions, such as thresholds or comparisons. SWITCH(TRUE,...) can imitate IFS, but that pattern may be less immediately readable to beginners.
SWITCH versus CHOOSE
CHOOSE selects by position:
=CHOOSE(A2,"Bronze","Silver","Gold")Use it when A2 is a reliable sequential index. Use SWITCH when the input contains meaningful codes:
=SWITCH(A2,"B","Bronze","S","Silver","G","Gold","Unknown")SWITCH versus XLOOKUP
Use SWITCH for a short mapping that belongs inside the formula. Use XLOOKUP when the mapping should be maintained as worksheet data:
=XLOOKUP(A2,$H$2:$H$10,$I$2:$I$10,"Unknown")In this example, codes are in H2:H10 and their labels are in I2:I10. A table is usually easier to maintain when the list is long, changes frequently, is reused by many formulas, or must be edited by nontechnical users. Microsoft documents exact matching as the default mode for XLOOKUP.
Best Value
- Stunning 15.6" FHD IPS Display: Experience crisp 1920x1080 resolution on this 15.6 inch laptop with an IPS panel that delivers wide viewing angles and vivid colors. The narrow-bezel design maximizes screen real estate for comfortable viewing on this Win 11 laptop, whether you're studying or working.
- Celeron J4105 Processor & 256GB SSD: Powered by a reliable Celeron J4105 processor paired with 12GB DDR4 memory and a fast 256GB M.2 SSD. This laptop computer supports SSD expansion up to 2TB and TF card expansion up to 1TB, so your storage grows with your needs. Delivers smooth multitasking for daily productivity.
- AI-Powered Win 11 Laptop: Built-in AI features enhance your productivity with smart assistance for writing, summarizing, and task management. Pre-installed with Win 11 and includes Office 365 subscription. This student laptop is backed by 1-year warranty and 24/7 customer support.
- All-Day 7000mAh Battery & 180° Hinge: The high-capacity 7000mAh battery keeps this laptop powered through long classes or meetings. The 180-degree lay-flat hinge lets you share your screen effortlessly during presentations. This durable laptop computer adapts to your dynamic workflow.
- Versatile Connectivity Hub: Equipped with USB 3.2, Type-C, Mini HDMI, and 3.5mm audio jack to connect all your peripherals. Stay online anywhere with high-speed 5G WiFi and Bluetooth 4.2. This college laptop keeps you connected at home, in the library, or on the go.
Common mistakes and fixes
Missing the default
Problem: An unexpected input produces #N/A.
Fix: Add a final fallback:
=SWITCH(A2,"A","Excellent","B","Good","Unknown grade")Using unpaired arguments
This formula is misleading:
=SWITCH(A2,"A","Excellent","B")The final "B" is interpreted as the default, not as a candidate value waiting for a result. Correct it by supplying the missing result:
=SWITCH(A2,"A","Excellent","B","Good","Unknown grade")Putting broad conditions first
With SWITCH(TRUE,...), Excel returns the result for the first condition that evaluates to TRUE. Put higher thresholds or more specific conditions first so a broad condition does not capture values intended for a later branch.
Hard-coding a mapping that should be a table
A formula with dozens of pairs may work, but it becomes difficult to update and review. Move a growing or frequently changing mapping into two worksheet columns and use XLOOKUP.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteExpecting partial matches
SWITCH compares the expression with listed values. It is not designed for general substring or wildcard searches. If the requirement is “contains this phrase” or “starts with these characters,” use separate text-search conditions or another suitable lookup design.
Confusing Excel with Access or VBA
This article covers the Excel worksheet SWITCH function. Microsoft also documents an Access Switch function with different syntax: it evaluates expression/value pairs and returns the value associated with the first expression that evaluates to True. Do not assume the Access or VBA form can be copied directly into an Excel worksheet.
See Microsoft’s Access Switch function documentation for that separate function.
Availability and compatibility
Microsoft’s function-specific documentation lists Excel for Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019, and corresponding Mac versions among the supported environments. It also describes availability with Office 2019 or a Microsoft 365 subscription.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallMicrosoft’s broader function-category page displays a 2016 version marker for SWITCH, while the function-specific page’s “Applies to” list begins with Excel 2019. Because those official pages do not present identical version information, avoid making an unqualified claim that every Excel 2016 installation supports the function.
For a shared workbook, test the formula in the oldest Excel edition and build that recipients must use. Excel for the web is listed as supported, although desktop and browser workflows are not identical in every respect.
Quick Recap
Practical decision guide
- Are you comparing one value with a short list of exact codes? Use
SWITCH. - Are you testing thresholds or multiple independent conditions? Start with
IFSorIF; useSWITCH(TRUE,...)when its order is clear to your audience. - Is the input a numeric position in a fixed list? Consider
CHOOSE. - Is the mapping long, shared, or frequently edited? Put it in a worksheet table and use
XLOOKUP. - Could the input be unexpected? Add a default result and decide explicitly how blanks should be handled.
- Must the workbook work in older Excel installations? Test the exact versions and builds before distributing it.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
