Switch is a useful function in Excel, that is a natural extension of writing about IFS last week.
While it can handle up to 126 pairs, there are far better options if you have that many! SWITCH is probably a bit easier to understand than IFS but it only does exact matching (there is a work around, but I can’t see any reason why you would do it instead of using IFS). It is a matter of using the best formula for the situation.
=SWITCH(Value to switch, Value to match1…[2-126], Value to return if there’s a match1…[2-126], Value to return if there’s no match)
Example: SWITCH(A1, 1, “Excellent”, 2, “Good”, 3, “OK”, “Not good”). So, if the value in cell A1 is 1 then put Excellent, if it is 2, put good, if it is 3 then put OK, if it is none of those then put Not good.
One thing to also note about SWITCH is that is doesn’t stop once it has found a match. It keeps going and checks the match against all the options, so if you have a lot of options, or a lot of SWITCH functions it can slow your spreadsheet down.
So, it you are after an easy to understand way of changing a set of values to another set of values then check out the SWITCH function. Just don’t get carried away and don’t forget there are other options out there that might be better for your situation, IFS, IF, CHOOSE, XLOOKUP and more.