Using IFS Function in Google Sheets to Test Multiple Conditions

In Google Sheets, you can use the IFS function to test multiple conditions at once and then return the result based on it.

The difference between the IF function and the IFS function is that in IFS function, you can test multiple conditions at once. As soon as the condition is found to be TRUE, its corresponding value is returned.

When to Use Excel IFS Function

IFS function can test multiple conditions and returns the value as soon as it finds the first condition that is fulfilled.

This makes this function ideal for situations where you need to find the grade for students based on their score. Another situation where it can come in handy is when you have to calculate the commission based on the sales values (these will be covered in examples below).

Let’s first have a look at the syntax of IFS function in Google Sheets.

Excel IFS Function – Syntax

=IFS(Condition1, Value1, [Condition2, Value2],…)

  • Condition1 – The first condition that is checked.
  • Value1 – The value to return if the first condition is TRUE.
  • [Condition2…] – These are optional arguments. You can specify additional conditions here. Note that every condition needs to be followed by the value to be returned for that condition.
  • [Value2….Value127] – These are optional arguments. Each value corresponds to its condition and would be returned if it’s condition is the first one to be TRUE.

Additional Notes:

  • IFS function would return the value of the first TRUE condition only. So you can have multiple conditions that are TRUE. However, only the value for the first one would be returned.
  • All the conditions in the IFS function must return either TRUE or FALSE. If it doesn’t, the formula will give an error.
  • If all the conditions in the IFS function return FALSE, the result of the formula would be the #N/A error. Since #N/A error is not very helpful in finding out what happened, you can use the last Condition as TRUE, and the value as FALSE or a descriptive text such as “No Match”.

Using Excel IFS Function – Examples

As I mentioned earlier, this function is best suited for situations where you need to check conditions in a sequence and return the value for the first condition that is met.

This makes it ideal for situations where you have to grade students or find commissions for the sales team.

Example 1 – Calculating Commission Based on the Sales

Suppose you’re the head of the sales department and it’s time to give out commissions based on the sales done by each employee.

Calculating Commission using IFS function in Google Sheets

Below is the formula that will give you the commission value for each sales personnel:

=IFS(B2<$E$3,$F$2,B2<$E$4,$F$3,B2<$E$5,$F$4,B2<$E$6,$F$5,B2>$E$6,$F$6)*B2

Calculating Commission using IFS function in Google Sheets Formula

Example 2 – Finding Student’s Grade Based on the Score

Suppose you have the scores for students in an exam as shown below (column A) and the grading conditions in column C and D.

Calculating Grades using IFS Formula in Google Sheets

Below is the formula that will give you the grades for each student:

=IFS(B2<$E$3,$F$2,B2<$E$4,$F$3,B2<$E$5,$F$4,B2<$E$6,$F$5,B2<$E$7,$F$6,B2>$E$7,$F$7)

Calculating Grades using IFS function in Google Sheets

You May Also Like the Following Google Sheets Tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X