How to Use the SWITCH Function in Excel

Excel’s SWITCH is a logical perform that’s primarily used for knowledge manipulation. It evaluates an expression in opposition to a listing of values, and returns a consequence similar to the primary matching worth.

Its reputation comes from its relative simplicity in comparison with different Excel capabilities that carry out comparable duties, which means it is simpler to learn the method and debug any points.

On this information, I will clarify how you can use the perform with a real-world instance, assess its advantages over different capabilities, and canopy a few of its limitations.

The Syntax for SWITCH

Earlier than I present you a real-life instance of SWITCH in motion, let’s take a look at the syntax:

=SWITCH(e,v1,r1,v2,r2,d)

the place

• e is the expression (the worth that can be evaluated),
• v1 is the primary worth to be in contrast in opposition to the expression,
• r1 is the consequence that can be returned if v1 matches e,
• v2 is the second worth to be in contrast in opposition to the expression,
• r2 is the consequence that can be returned if v2 matches e, and
• d (elective) is the default worth if e doesn’t match any of the v values.

Whereas there are solely two v–r pairings proven right here within the syntax (v1–r1 and v2–r2), you may have as much as 126 pairings total. On condition that SWITCH returns a consequence similar to the first matching worth, it is necessary to fastidiously take into account the order of your v–r pairings.

SWITCH in Motion

Let me present you the SWITCH perform in a real-world state of affairs. On this desk, I’ve a listing of scholars and their grades, and I must work out their subsequent steps based mostly on these grades.

Since there are three totally different grade prospects (A, B, and C), I would like to include all of those into the SWITCH method. So, in cell C2, I’ll kind

=SWITCH([@Grade],"A","Routinely advance to subsequent stage","B","Proceed at this stage","C","Transfer right down to earlier stage","GRADE REQUIRED")

Though this appears sophisticated at first, when damaged down, it is truly fairly logical:

• First, I need Excel to judge the expressions within the Grade column of my formatted desk, which is why I typed [@Grade] as worth e.
• Then, I’ve three v–r pairings: “A” will return “Routinely advance to subsequent stage,” “B” will return “Proceed at this stage,” and “C” will return “Transfer right down to earlier stage.”
• Lastly, after the ultimate pairing, I’ve said “GRADE REQUIRED” as worth d, which is the consequence if not one of the values (v#) matches the expression (e).

After urgent Enter, as a result of my knowledge is in a formatted Excel table, the remainder of column C will populate mechanically.

Discover how cell C8 comprises “GRADE REQUIRED,” as a result of the expression in cell B8 didn’t match any of the values in my SWITCH method.

Why Use SWITCH As an alternative of IF, IFS, or XLOOKUP?

It’s possible you’ll be questioning why you’ll use SWITCH over a few of Excel’s different capabilities that carry out comparable actions, corresponding to IF, IFS, and XLOOKUP. Listed here are some causes:

Keep away from Repeating the Expression

To create the identical ends in the desk above utilizing IF or IFS, I must repeat the expression every time:

=IFS([@Grade]="A","Routinely advance to subsequent stage",[@Grade]="B","Proceed at this stage",[@Grade]="C","Transfer right down to earlier stage")

With the SWITCH perform, nevertheless, I solely should state the expression as soon as initially of the method:

=SWITCH([@Grade],"A","Routinely advance to subsequent stage","B","Proceed at this stage","C","Transfer right down to earlier stage","GRADE REQUIRED")

Because of this, the SWITCH perform is less complicated to learn, much less liable to typing errors, and simpler to assessment if there’s a problem.

Preserve The whole lot in One Place

Very like SWITCH, the XLOOKUP perform compares an expression to a listing of values, and returns a corresponding worth. Nonetheless, with XLOOKUP, the checklist of values is in a separate desk, whereas SWITCH incorporates all of them into one method. Because of this you haven’t any floating knowledge, and so, your Excel spreadsheet stays tidy.

One Set of Parentheses

If I selected to make use of nested IF capabilities to realize the identical outcomes within the desk above, I’d have had to make use of a brand new set of parentheses for every IF argument:

=IF([@Grade]="A","Routinely advance to subsequent stage",IF([@Grade]="B","Proceed at this stage",IF([@Grade]="C","Transfer right down to earlier stage","GRADE REQUIRED")))

Because of this, the method ends confusingly with three closing parentheses, and debugging any points with the syntax could be more difficult. Alternatively, when used at its most elementary stage with out another further capabilities, SWITCH requires just one pair of parentheses.

SWITCH Drawbacks

Whereas SWITCH has many advantages, there are a number of limitations to remember earlier than you get working in your Excel spreadsheet:

• You may’t use operators (corresponding to ) or approximate matches with the usual SWITCH syntax. As an alternative, SWITCH is restricted to precise matching solely.
• When you’ve got numerous potential values and outcomes, developing your SWITCH method within the first occasion will take ceaselessly. Personally, I’d advocate utilizing not more than seven or eight value-result pairings in your SWITCH method.
• SWITCH is a comparatively rigid perform. For instance, XLOOKUP can return complete rows and columns of knowledge, reasonably than only a single worth.
• As a result of SWITCH requires numerous commas (and quotes for those who’re together with non-numerical values), it is simple to make a mistake for those who kind the method manually.

SWITCH is simply one of many many various ways to use data in an Excel table. You may additionally think about using INDEX to find values, MATCH to find a value’s position, or INDEX and MATCH together to create a two-way lookup.