Home : Case
Q14493 - INFO: Case
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

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 3/1/2018 9:16 AM.
Last Modified on 9/4/2018 8:27 AM.
Last Modified by Scott Dorner.
Article has been viewed 760 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article