Using the Switch Function to change values to text or the oposite

Easily add values of certain cells meeting certain cirteria.
Català - Castellano - Deutsch
The SWITCH function in Excel is a logical function that returns a value based on a list of expressions and corresponding values. The function takes the following syntax:

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

The 'expression' is the value that you want to test, 'value1' and 'result1' are the first test and corresponding result, [value2, result2] are the second test and result, and so on. If none of the tests are true, the function will return the 'default' value.

Here's an example of how you can use the SWITCH function in a practical situation:

Suppose you have a list of product codes in column A and you want to return the corresponding product name in column B. You can use the SWITCH function to do this. In cell B2, you would enter the following formula:

=SWITCH(A2, "P1", "Product 1", "P2", "Product 2", "P3", "Product 3", "P4", "Product 4", "Unknown")

In this example, the 'expression' is the value in cell A2, and the 'value1', 'result1', 'value2', 'result2', etc. are the product codes and corresponding product names. The last value, "Unknown", is the default value that will be returned if the product code in cell A2 does not match any of the codes listed in the formula.

You can then drag the formula down to the rest of the cells in column B to fill in the product names for the rest of the product codes in column A.

Another example:

Suppose you have a list of student grades in column A, and you want to return the corresponding letter grade in column B. You can use the SWITCH function to do this. In cell B2, you would enter the following formula:

=SWITCH(A2, 90, "A", 80, "B", 70, "C", 60, "D", "F")

In this example, the 'expression' is the value in cell A2, and the 'value1', 'result1', 'value2', 'result2', etc. are the grade thresholds and corresponding letter grades. The last value, "F", is the default value that will be returned if the grade in cell A2 is less than 60.

You can then drag the formula down to the rest of the cells in column B to fill in the letter grades for the rest of the student grades in column A.

You can also use the SWITCH function in combination with other functions in Excel, such as IF and VLOOKUP, to create more complex formulas.

As you can see, the SWITCH function can be a powerful tool for organizing and analyzing data in Excel. By using the function with logical tests and corresponding results, you can quickly and easily return specific values based on a given input.


Tweet