Excel's SWITCH function: efficient data processing tool

Excel's SWITCH function is a logical function, mainly used for data processing. It compares an expression with a series of values ??and returns the result corresponding to the first matching value.

SWITCH functions are popular because they are simpler than other Excel functions that perform similar tasks, which means it is easier to read formulas and debug any problems.

This guide will explain how to use this function with a practical example, evaluate its advantages over other functions, and introduce some of its limitations.

Microsoft added the SWITCH function to Excel in 2016, so it was not available in earlier versions. If you try to use SWITCH in an incompatible version, Excel returns a #NAME? error.

SWITCH function syntax

Before demonstrating the practical application of the SWITCH function, let's take a look at its syntax:

<code>=SWITCH(e,v1,r1,v2,r2,d)</code>

Of:

• e is the expression (the value to be evaluated),
• v1 is the first value to be compared with the expression,
• r1 is the result returned if v1 matches e,
• v2 is the second value to be compared with the expression,
• r2 is the result returned if v2 matches e, and
• d (optional) is the default value if e does not match any v value.

Although only two v-r pairs (v1-r1 and v2-r2) are shown in the syntax here, you can use up to 126 pairs. Given that SWITCH returns the result corresponding to the first matching value, it is important to carefully consider the order of v-r pairs.

If you do not include optional d and no value (v#) matches the expression (e), Excel returns a #N/A error.

Practical application of SWITCH function

Let me show you the SWITCH function in a real scenario. In this table, I list the students and their grades and I need to determine their next steps based on these grades.

I need to include all of this in the SWITCH formula since there are three different possibilities for grades (A, B, and C). So, in cell C2, I will type:

<code>=SWITCH([@Grade],"A","自動(dòng)晉級(jí)到下一等級(jí)","B","繼續(xù)保持當(dāng)前等級(jí)","C","降級(jí)到上一等級(jí)","需要成績(jī)")</code>

If any value or result in the SWITCH formula is not a number, it needs to be enclosed in quotes.

Although this looks complicated at first glance, it is actually very logical after decomposition:

• First, I want Excel to evaluate the expression in the formatted table "Grade" column, which is why I use [@Grade] as the value e.
• Then I have three v-r pairs: "A" will return "Automatically advance to next level", "B" will return "Continue to current level", and "C" will return "Downgrade to previous level" .
• Finally, after the last pairing, I take "required grade" as the value d, and if no value (v#) matches the expression (e) then returns this result.

After pressing Enter, the rest of column C will be automatically populated because my data is in the formatted Excel table.

Note that cell C8 contains "required grades" because the expression in cell B8 does not match any of the values ??in my SWITCH formula.

If I want to change the output in column C, I will return to cell C2, modify the formula in formula bar, and press Enter. This change will be automatically applied to other cells in column C.

Why use SWITCH instead of IF, IFS or XLOOKUP?

You may be wondering why you are using SWITCH instead of other functions in Excel that do similar operations, such as IF, IFS, and XLOOKUP. Here are some reasons:

Avoid repeating expressions

To create the same result in the above table using IF or IFS, I have to repeat the expression every time:

<code>=SWITCH(e,v1,r1,v2,r2,d)</code>

However, using the SWITCH function, I only need to declare the expression once at the beginning of the formula:

<code>=SWITCH([@Grade],"A","自動(dòng)晉級(jí)到下一等級(jí)","B","繼續(xù)保持當(dāng)前等級(jí)","C","降級(jí)到上一等級(jí)","需要成績(jī)")</code>

Therefore, the SWITCH function is easier to read, less prone to input errors, and is easier to check if there is a problem.

Save everything in one place

Similar to SWITCH, the XLOOKUP function compares an expression with a series of values ??and returns the corresponding value. However, with XLOOKUP, the list of values ??is in a separate table, and SWITCH combines all of this into a single formula. This means you don't have any floating data, so your Excel spreadsheets stay neat.

A set of brackets

If I choose to use nested IF functions to achieve the same result in the above table, I will have to use a new set of brackets for each IF parameter:

<code>=IFS([@Grade]="A","自動(dòng)晉級(jí)到下一等級(jí)",[@Grade]="B","繼續(xù)保持當(dāng)前等級(jí)",[@Grade]="C","降級(jí)到上一等級(jí)")</code>

As a result, the formula ends in confusion with three close brackets, and debugging any syntax problems will be more challenging. On the other hand, when used at its most basic level without other additional functions, SWITCH only requires a pair of brackets.

Disadvantages of SWITCH function

While SWITCH has many advantages, there are some limitations to keep in mind before starting to work on Excel spreadsheets:

• You cannot use operators (for example) or approximate matching with standard SWITCH syntax. Instead, SWITCH is limited to exact matches.
• If you have a lot of potential values ??and results, it will take a long time to build the SWITCH formula first. Personally, I recommend using up to seven to eight values-result pairs in the SWITCH formula.
• SWITCH is a relatively inflexible function. For example, XLOOKUP can return data for the entire row and column, not just a single value.
• Because SWITCH requires a lot of commas (quotation marks are required if non-numeric values ??are included), it is easy to go wrong if you type the formula manually.

SWITCH is just one of many different ways to use data in Excel tables. You can also consider using INDEX to find values, using MATCH to find bits of values, or combining INDEX and MATCH to create a bidirectional lookup.

The above is the detailed content of How to Use the SWITCH Function in Excel. For more information, please follow other related articles on the PHP Chinese website!