CASE returns a value based on a list of conditions (similar to an IF statement)
SYNTAX:
CASE(TestValue, Condition1, Result1, ConditionX, ResultX,Condition9, Result9, ResultElse)
TestValue: The value being tested. Can be a constant, cell reference or formula. Can be Text or Numeric.
ConditionX: The comparison to test the TestValue against. If the Condition is meet, the corresponding ResultX will be returned. Conditions are tested in the order listed and once a condition is found to be true the Result is returned and no other conditions are evaluated. Is a string with the following syntax (test:value). Test can have the following values. Value is text or number to test against.
| Test |
Examples |
Description |
| = |
"=:4", "=:Scott" |
If the test value equals 4
If test value equals "Scott" (not case sensitive, i.e. if test value is scott, SCOTT, or Scott will all be true)
NOTE: Equal is assumed as the test if it is not specified. Example, "Scott" would be interpreted as "=:Scott" |
| CS= |
"CS=:Scott" |
Case Sensitive equals "Scott". SCOTT, scott, ScoTT will NOT be true, only Scott. |
| STARTS |
"STARTS:New" |
Starts with New. If TestValue is New York, new mexico, or New Delhi will all be true. |
| CS.STARTS |
"CS.STARTS:New" |
Case Sensitive starts with. If TestValue is "New York" would be true but "new mexico" would be false. |
| ENDS |
"ENDS:Bacti" |
Ends with Bacti. If TestValue is "DS-270.BACTI" would be true. |
| CS.ENDS |
"CS.ENDS:Bacti" |
Case sensitive ends with. |
| CONTAINS |
"CONTAINS:BOB" |
Contains BOB anywhere in string. If TestValue is "Jim Bob Cooper" would return true. |
| CS.CONTAINS |
"CS.CONTAINS:BOB" |
Case sensitive contains. |
| < |
"<:4" |
Less than 4. If Testvalue is 3 would be true |
| <= |
"<=:4" |
Less than or equal to 4. If Testvalue is 3 would be true |
| > |
">:4" |
Greater than 4. |
| >= |
">=:4" |
Greater than or equal to 4. |
Resultx: The value to return if the corresponding Conditionx is true.
ResultElse: Optional. When no conditions are met the Result to return. If omitted, function will return "".
EXAMPLES:
=CASE(7,1,"ONE",2,"Two",3,"Three",4,"Four",5,"Five",6,"Six",7,"Seven",8,"Eight",9,"Nine","Not a Digit") returns "Seven"
=CASE(B12,"ENDS:UP","Repeat-Upstream","ENDS:Down","Repeat-Downstream","ENDS:RT","Routine","ENDS:NM","New Main","Not a Distribution Sample")
=VAL(CASE(B19,"<=:1",0,"<=:4",1,"<:50",2,"<:100",3,4)) - Val is used to convert the value returned to a number. Case function returns text by default.
VIDEO:
https://youtu.be/xSCxF82ogZ8
SEE ALSO: If spread function