The SWITCH function in Excel is a logical function that evaluates an expression and returns a value based on a list of possible matches. The syntax of the SWITCH function is:
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])
The SWITCH function takes the following arguments:
- expression: The value or expression to be compared with the list of values.
- value1, value2, …: The values to be matched with the expression. You can specify up to 126 pairs of values and results.
- result1, result2, …: The results to be returned if the expression matches the corresponding value. The results can be any valid Excel value, such as text, numbers, dates, or formulas.
- default: (Optional) The value to be returned if the expression does not match any of the values. If omitted, the function returns the #N/A error.
The SWITCH function works by comparing the expression with the first value in the list. If they match, the function returns the first result. If they do not match, the function moves on to the next pair of value and result, and so on, until it finds a match or reaches the end of the list. If no match is found, the function returns the default value or the #N/A error.
How to Use the SWITCH Function to Compare Two Cells in Excel
One of the applications of the SWITCH function is to compare two cells in Excel and return a different value based on the comparison. For example, suppose you have a table of sales data for different products and regions, as shown below:
Table
| Product | Region | Sales |
|---|---|---|
| A | X | 100 |
| B | Y | 200 |
| C | Z | 300 |
| D | X | 400 |
| E | Y | 500 |
| F | Z | 600 |
You want to create a new column that shows the performance of each product based on the sales and region. You have the following criteria:
- If the product is A or B, and the region is X or Y, the performance is “Good”.
- If the product is C or D, and the region is Y or Z, the performance is “Better”.
- If the product is E or F, and the region is Z, the performance is “Best”.
- Otherwise, the performance is “Average”.
To achieve this, you can use the SWITCH function to compare the product and region cells and return the corresponding performance value. The formula in cell D2 is:
=SWITCH(B2&C2, "AX", "Good", "AY", "Good", "BX", "Good", "BY", "Good", "CY", "Better", "CZ", "Better", "DY", "Better", "DZ", "Better", "EZ", "Best", "FZ", "Best", "Average")
This formula concatenates the product and region cells using the & operator, and then compares the result with the list of values and results in the SWITCH function. The formula returns “Good” for the first row, as shown below:
| Product | Region | Sales | Performance |
|---|---|---|---|
| A | X | 100 | Good |
| B | Y | 200 | Good |
| C | Z | 300 | Better |
| D | X | 400 | Average |
| E | Y | 500 | Average |
| F | Z | 600 | Best |
You can copy the formula down to the rest of the rows to get the performance for each product and region.
Other Approaches to Compare Two Cells in Excel
The SWITCH function is not the only way to compare two cells in Excel and return a different value based on the comparison. You can also use other functions, such as IF, IFS, CHOOSE, or VLOOKUP, depending on your needs and preferences. Here are some examples of how to use these functions to achieve the same result as the SWITCH function in the previous section:
- Using the IF function:
=IF(OR(AND(B2="A", OR(C2="X", C2="Y")), AND(B2="B", OR(C2="X", C2="Y"))), "Good", IF(OR(AND(B2="C", OR(C2="Y", C2="Z")), AND(B2="D", OR(C2="Y", C2="Z"))), "Better", IF(AND(B2="E", C2="Z"), "Best", IF(AND(B2="F", C2="Z"), "Best", "Average"))))
- Using the IFS function:
=IFS(OR(AND(B2="A", OR(C2="X", C2="Y")), AND(B2="B", OR(C2="X", C2="Y"))), "Good", OR(AND(B2="C", OR(C2="Y", C2="Z")), AND(B2="D", OR(C2="Y", C2="Z"))), "Better", AND(B2="E", C2="Z"), "Best", AND(B2="F", C2="Z"), "Best", TRUE, "Average")
- Using the CHOOSE function:
=CHOOSE(MATCH(B2&C2, {"AX", "AY", "BX", "BY", "CY", "CZ", "DY", "DZ", "EZ", "FZ"}, 0), "Good", "Good", "Good", "Good", "Better", "Better", "Better", "Better", "Best", "Best", "Average")
- Using the VLOOKUP function:
=VLOOKUP(B2&C2, {"AX", "Good"; "AY", "Good"; "BX", "Good"; "BY", "Good"; "CY", "Better"; "CZ", "Better"; "DY", "Better"; "DZ", "Better"; "EZ", "Best"; "FZ", "Best"}, 2, FALSE)
Note that for the CHOOSE and VLOOKUP functions, you need to enter the list of values and results as an array constant or a named range, respectively.
