• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7938
  • Last Modified:

Case statements in Excel 2010?

Is it possible to use the equivalent of a case statement in Excel 2010? By that I mean can an Excel command be used to produce different results based on the data contents of the cell. Some database products can do case one, case two, case three, etc. and by the software picking the correct case, giving you a different answer or calculation for each case.
0
mzimerman
Asked:
mzimerman
6 Solutions
 
Patrick MatthewsCommented:
To an extent, yes.

For example, you can use IF() to do this:

=IF(<logical test>,<expression if true>,<expression if false)

You can also embed IF() expressions:

=IF(A1="yes",1,IF(A1="no",2,IF(A1="maybe",3,4)))

If the possible values you need to test are always 1,2,3,...,N then you can use CHOOSE():

=CHOOSE(A1,"one","two","three","four")

In VBA, the function that comes closest is probably Switch(), but that is unavailable in Excel unless you create a UDF wrapper for it.
0
 
SmittyNetCommented:
Via code or formula?  If by formula, you can certainly use nested "IF" statements to do the job.  Via code - Select Case will do the trick.  I can provide examples once you clarify what you're attempting to do.
0
 
Patrick MatthewsCommented:
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
DustinKikuchiCommented:
There's no SWITCH command per se, that I am aware of, but the functionality can be replicated using IF statements and, depending on the scenario, some other options such as LOOKUP, MATCH, etc.

Do you have a specific example you can explain or post?
0
 
Patrick MatthewsCommented:
Note that for my suggestions above I was assuming you are trying to do this in Excel formulas.  In VBA, as SmittyNet indicates, you have the If...ElseIf...Else and Select Case control structures.
0
 
Martin LissOlder than dirtCommented:
As a macro which can be use as a user defined formula. eg =MyMacro()

Function MyMacro()

Select Case Range("A1").Value
Case 1
    ' Do something
Case < 8
    ' Do something else
Case Else
    ' Do default processing
End Select

End Function

Open in new window

0
 
Martin LissOlder than dirtCommented:
Is there anything more we can do for you here?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now