IFS Function in Google Sheets: Easy Guide

Sumit Bansal

Last updated December 27, 2022

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 IFS Google Sheets guide, 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).

Table of Contents

When to Use the IFS Function

The IFS formula in Google Sheets is useful when you need to analyze multiple conditions at one go. You could imagine it as the Google Sheets IF else function.

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

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

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

How to Use the IFS Function (IFS 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

Basic IFS Example – 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.

Here’s how to use IFS in Google Sheets to get 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)

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)

Advanced IFS Example – Calculate Commission Based on Sale Value

Just like the above example, we are going to solve multiple IF statements in Google Sheets with the IFS function. 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.

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

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. Google Sheets IFS checks multiple conditions while IF only checks one.

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 assess a few conditions, use the IF function (or the IFS function), but if you have to assess many conditions, better to use the IFS formula.

Comparison of IFS Vs. Nested IF Functions as an Alternative Solution

You could get similar results by using several IF functions in one formula instead of one IFS function. But, there is more room for error by increasing the amount of brackets you need to include and extra text. Here is an example of using Google Sheets IF with multiple conditions as a nested function to show grades instead of IFS:

=IF(B2>90,"A",IF(B2>70, "B",IF(B2>50,"C",IF(B2<50,"F"))))

And here is the equivalent IFS function:

=IFS(B2>90,"A",B2>70,"B",B2>50,"C",B2<50,"F")

As you can see, the IFS function is much shorter and neater than using nested IF functions as an else IF Google Sheets function.

Frequently Asked Questions

What is the IFS Function?

IFS is part of the IF statement family of functions in Google Sheets that searches if multiple parameters are true or false and returns a result based on the retrieved response. It works similar to IF, IFNA, and IF Contains.

Can You Nest IFS in Google Sheets?

The whole point of IFS is avoiding nested IF statements or using AND and OR. But you could nest an IFS statement in an ARRAYFORMULA or similar.

What Is the Difference Between IF and IFS in Google Sheets?

IF statements can only compare one true or false statement at a time, whereas Google Sheets IFS can check multiple. For example an IF function may return “Pass” for a true result if you tell it to check if a score is over 50. An IFS statement could return more grades with a formula such as this:

=IFS(B2>90,"A",B2>70,"B",B2>50,"C",B2<50,"F")

You can also use nested IF functions for the same purpose, but IFS is neater and quicker once you get the hang of it.

What Can I Use Instead of IFS Function?

You can instead use nested IF functions. You can nest additional IF statements in a single formula, or you could use the AND or OR functions inside a parent IF function. You could also use a VLOOKUP formula to search for results instead.

Hope you found this tutorial useful!

Other Google Sheets tutorials you may like: