In an earlier blog, I have introduced SWITCH function to replace IF. Then a condition was for an exact value, like switch “P1” value to “Jan”.

Now we have bit different SWITCH case. If the value is less than 10, then the returned value is “small”, if the value is from 11 to 14, then returned value is “mid”, the values from 15 to 19 are “fair”,  and above, the returned value is “large”.

This can be tackled with IF as follows:

=IF(C4<9;”small”;IF(C4<15;”mid”;IF(C4<19;”fair”;”large”)))

Same calculus can be done with SWITCH:

=SWITCH(TRUE;C4<10;”small”;C4<15;”mid”;C4<20;”fair”;”large”)

Earlier, the first argument in SWITCH was cell where the value is, like C4 in our case. However, when we don’t have exact values but less than operator, the first argument is TRUE. Then we have condition if C4 is less than 10, then the returned value is “small”. If C4 is less than 15, then the sentence returns value “mid”. In case C4 value ranges from 15 to 19, then SWITCH returns “fair”. For value 20 and above, the returned value is “large”.

Let’s check a case where we have two input parameters and need to evaluate combination of the two parameters.

Column C may have values 1 or 2, column D may have values a or b. If C equals to 1 and D equals to a, the result is one a. When C is 1 and d is b, the outcome is one b. If C equals to 2 and D a, then result is two a. In case C is 2 and D is b, then the right value is two b.

The normal IF and AND combination works.

=IF(AND(B4=1;C4=”a”);”one a”;IF(AND(B4=1;C4=”b”);”one b”;IF(AND(B4=2;C4=”a”);”two a”;IF(AND(B4=2;C4=”b”);”two b”))))

Same results can be returned with SWITCH.

=SWITCH(TRUE;(B4&C4= “1a”);”one a”;(B4& C4=”1b”);”one b”;(B4&C4= “2a”);”two a”;(B4& C4= “2b”);”two b”;”else”)

The key is that for value, we need to combine B4 and C4. Like when combination of B4 and C4 equals to 1a, then the result is one a. Just remember to write value inside parenthesis. Similarly, define all the four combination and the wanted result.

Both IF and SWITCH returned correct values. Still, I prefer SWITCH as we need IF several times and then we need also several parenthesis. When writing a SWITCH sentence, it is easier to have one block in clipboard, paste the block and modify it.