Author: Oscar Cronquist Article last updated on July 04, 2022

The SELECT CASE statement allows you to compare an expression to multiple values.

It is similar to the IF THEN ELSE statement except that the IF THEN ELSE statement can evaluate multiple expressions. The SELECT CASE statement evaluates one expression.

1. SELECT CASE statement Syntax

Select Case testexpression

[ Case expressionlist-n [ statements-n ]]
[ Case Else [ elsestatements ]]

End Select

Back to top

2. SELECT CASE statement Parameters

testexpression Required. Any expression:

numeric expression - You can use variables, constants, keywords, and operators. The result is a number.

string expression - Any expression that returns a string.
expressionlist-n Required.

True - ignore empty cells in the third argument.

False - adds empty cells to the result.
statements-n Required. The cell range you want to concatenate.
elsestatements Optional. Up to 254 additional cell ranges.

Back to top

3. SELECT CASE statement example 1

The User Defined Function above compares the score to several criteria and returns the corresponding grade.

The UDF is entered in column D and takes the score from column C and returns the grade.

UDF in cell D3:

=GRADE(C3)

VBA Code

Function Grade(score)

Select Case score
Case Is < 60
Grade = "F"
Case 61 To 64
Grade = "D"
Case 65 To 69
Grade = "D+"
Case 70 To 74
Grade = "C"
Case 75 To 79
Grade = "C+"
Case 80 To 84
Grade = "B"
Case 85 To 89
Grade = "B+"
Case 90 To 94
Grade = "A"
Case Else
Grade = "A+"
End Select

End Function

Back to top

4. SELECT CASE statement example 2

How to use the SELECT CASE statement1

Formula in cell D3:

=PriceSize(B3)

VBA Code

Function PriceSize(size)

Select Case size
    Case Is = "Small"
        PriceSize = 100
    Case Is = "Medium"
        PriceSize = 120
    Case Is = "Large"
        PriceSize = 135
    End Select
    
End Function

5. SELECT CASE statement - VBA alternative

How to use the SELECT CASE statement vba alternative

Formula in cell D3:

=Grade1(C3)

VBA Code

Function Grade1(score)

If score < 60 Then
    Grade1 = "F"
    
ElseIf score <= 64 And score >= 61 Then
    Grade1 = "D"
    
ElseIf score <= 65 And score >= 69 Then
    Grade1 = "D+"
    
ElseIf score <= 74 And score >= 70 Then
    Grade1 = "C"

ElseIf score <= 79 And score >= 75 Then
    Grade1 = "C+"
    
ElseIf score <= 84 And score >= 80 Then
    Grade1 = "B"

ElseIf score <= 89 And score >= 85 Then
    Grade1 = "B+"

ElseIf score <= 94 And score >= 90 Then
    Grade1 = "A"

Else
    Grade1 = "A+"
    
End If

End Function

6. SELECT CASE statement - Excel Function alternative

How to use the SELECT CASE statement 1

Formula in cell D3:

=SWITCH(TRUE, C3<60, "F", C3<=64, "D", C3<=69, "D+", C3<=74, "C", C3<=79, "C+", C3<=84, "B", C3<=89, "B+", C3<=94, "A", C3>94, "A+")

Get Excel *.xlsm file

How to use SELECT CASE statement.xlsm

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.