Case statements in Excel 2010?

Posted on 2012-08-15
Last Modified: 2012-08-21
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.
Question by:mzimerman
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    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 the possible values you need to test are always 1,2,3,...,N then you can use CHOOSE():


    In VBA, the function that comes closest is probably Switch(), but that is unavailable in Excel unless you create a UDF wrapper for it.

    Assisted Solution

    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.
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    LVL 4

    Assisted Solution

    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?
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    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.
    LVL 44

    Accepted Solution

    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

    LVL 44

    Expert Comment

    by:Martin Liss
    Is there anything more we can do for you here?

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now