• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6386
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
Is there anything more we can do for you here?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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