The SWITCH function is a useful tool for categorizing data. In the right circumstances, it can save you from messy, nested IF functions.
The SWITCH function is used to test an expression against a list of cases. It returns a value when the expression is equal to one of the cases.
It has some similarities to the IFS function but differs because SWITCH tests for exact matching rather than whether a condition is true (e.g. X > Y). SWITCH also has a default option to return a value if no match is found.
Let’s see an example. Suppose we have this data set of student grades and we want to add context to each grade level.
Use this SWITCH function to categorize these grades:
=SWITCH(B2,
"A","Top marks! Great job!",
"B","Keep up the good work",
"C","Could do better",
"Failed. Remedial study needed")Let’s break it down:
B2 is the condition we’re going to test. It’s the grade letter from column B and it’s the input to our SWITCH function.
We check the value of B2 against the first case “A”. If they match (i.e. the grade in B2 was also “A”) then SWITCH returns the string “Top marks! Great job!”.
If the value from B2 doesn’t match “A”, we move on and test it against “B” and if that fails, test against “C”.
If the condition doesn’t match any of the cases, the last string is returned: “Failed. Remedial study needed”. It’s our catch-all solution. Anything that’s not “A”, “B” and “C” will return this answer.
This is what the solution looks like:
Give it a try and SWITCH things up!
Ben Collins, “F”, Remedial Study required to spell Remedial correctly. 🙂
Indeed! Fixed now
Hadn’t found SWITCH yet, nice article. I would probably just use INDEX MATCH, @Ben, what would be the main use case for SWITCH over INDEX MATCH in your mind?
I think SWITCH is easier to understand for non-technical spreadsheet users, so it’s a good choice for simple examples like the one shown here. But in general, INDEX-MATCH is much more powerful and scalable, so better for working with datasets.
Dows this formula works with ArrayFormula?