How to Write a Case Statement in Google Sheets

A Case Statement in Google Sheets is not a built-in function, but you can achieve similar functionality using the IF or IFS functions. Let's see how to use the IF and IFS functions to create a case statement-like behavior in Google Sheets.

Using IF function

The IF function is used to make decisions based on a condition. Here's the syntax for the IF function:

=IF(logical_expression, value_if_true, value_if_false)

To create a case statement using the IF function, you can use nested IF functions. Let's consider an example.

Example

Suppose we have a list of grades, and we want to assign the corresponding remarks based on the grades. Here's how we can use nested IF functions in Google Sheets as a case statement:

1. Create a new Google Sheet.
2. In column A, add the following grades:

A
B
C
D
F

3. In cell B1, enter the following formula:

=IF(A1="A", "Excellent", IF(A1="B", "Good", IF(A1="C", "Average", IF(A1="D", "Poor", IF(A1="F", "Fail", "Invalid Grade")))))

4. Press Enter.
5. Now, copy the formula in cell B1 and paste it into cells B2 to B5.

The formula will display the corresponding remarks based on the grades in column A.

Using IFS function

The IFS function allows you to test multiple conditions without nesting multiple IF functions. Here's the syntax for the IFS function:

=IFS(condition1, result1, condition2, result2, ..., condition_n, result_n)

Let's use the IFS function to create a case statement in Google Sheets using the same example as above.

Example

1. In cell C1, enter the following formula:

=IFS(A1="A", "Excellent", A1="B", "Good", A1="C", "Average", A1="D", "Poor", A1="F", "Fail")

2. Press Enter.
3. Now, copy the formula in cell C1 and paste it into cells C2 to C5.

The formula will display the corresponding remarks based on the grades in column A, similar to the nested IF function example.