IFS Function in Google Sheets – Test Multiple Conditions (Examples)

When working with formulas in Google Sheets, testing conditions using an IF statement is quite useful. It allows you to check a condition and return a result based on whether the condition is TRUE or FALSE.

But when you have multiple conditions to test, IF function could quickly become long, ugly and unmanageable.

And that’s why Google Sheets came up with the IFS function.

With the IFS function in Google Sheets, you can test multiple conditions in the same formula (unlike the IF function which allows only one condition to be tested and need to be nested).

In this tutorial, I will show you how to use the IFS function in Google Sheets with a couple of examples (and all the other important things you need to know about it).

When to Use the IFS Function

IFS function is useful when you need to analyze multiple conditions at one go.

For example, you can use the IFS function in the following situations (these examples are covered later in this tutorial):

  1. To get the grade for a student based on the score
  2. To get the commission value for sales rep where the commission varies based on the sales value

Before we jump into the IFS examples, let’s quickly learn about the syntax of this function.

IFS Function in Google Sheets – Syntax

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

Where:

  • Condition1 – it’s the first condition that is checked by the function.
  • Value1 – It’s the value to return in case the first condition is TRUE.
  • [Condition2…Condition127] – You can use up to 127 optional arguments. You can specify additional conditions here. For every condition you specify, there also needs to be a value that would be returned in case the condition is TRUE
  • [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.

Some Important things to know about the Google Sheets IFS function

  • All the conditions used within the IFS function must return either TRUE or FALSE. And in case it doesn’t, the formula will give a #N/A error.
  • IFS function goes through the conditions one by one and stops when it finds the first TRUE condition. So you can have multiple conditions that return TRUE, but the function will only return the value for the first TRUE condition.
IFS function returns a #N/A (Not Available) error when all the specified conditions are false. Since #N/A error is generic and not very helpful in finding out what happened, you can use have TRUE as the last condition and something descriptive (such as “No Match”) as the value to return. This way, when you get the result as “No Match”, you would know that all the conditions were FALSE

Now let’s jump in and see a few examples of using the IFS function

Example 1 – Calculate Student’s Grade From the Score

Suppose you have the dataset as shown below and you want to calculate the grade for each student based on their marks. The grading criteria is also mentioned in the table on the right.

Dataset to get student grade using IFS function

Below is the formula that will give you the grade of each student based on their marks:

=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)

IFS formula to get the student grades

The above formula checks each score through a series of conditions. As soon as it finds a condition that is TURE, it returns the value corresponding to that condition.

For this to work, you check the scores in ascending order, i.e., first, check whether the score is less than 35 or not, then check whether it’s less than 50 or not, and so on.

IFS works well when you have a couple of conditions. When you have a lot, it can get long and complicated (less than the nested IF function, but still long). In such cases, you can think you replacing this whole IF/IFS formulas with a simple VLOOKUP instead.

The below formula will also work well and give you the score for each student (but it requires the score table to be sorted in ascending order):

=VLOOKUP(B2,$E$2:$F$7,2,TRUE)

Example 2 – Calculate Commission Based on Sale Value

Just like the above example, another useful use-case of using the IFS function is when you have to calculate commission based on the sales done by each sales rep.

Since commissions are usually dependent on how much sales you have done, we need to analyze each person’s sales value and calculate the commission for it.

Suppose you have the dataset as shown below and you want to calculate commission based on the commission table on the right.

Dataset to get commission using IFS function

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

=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)*B2

IFS formula to get commission value

While the IFS function would return the commission percentage, I have also multiplied it with the sales value to get the overall commission value.

IF Vs IFS Function in Google Sheet

Both IF and IFS are extremely useful functions and you would find yourself using these all the time.

The biggest difference between the IFS and the IF function is that with the IF function, you can specify what value to return in case the condition is FALSE. This is not in-built in the IFS function.

But on the other hand, IFS formulas can be simpler and shorter when you have multiple conditions to analyze.

Bottom line, if only have to asses a few conditions, use the IF function (or the IFS function), but if you have to asses many conditions, better to use the IFS formula.

Hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

Leave a Comment