Author: Oscar Cronquist Article last updated on May 02, 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

5. SELECT CASE statement - VBA alternative

6. SELECT CASE statement - Excel Function alternative

Get Excel *.xlsm file

How to use SELECT CASE statement.xlsm