The IFS Google Sheets function lets you easily test multiple conditions in a single formula. While the functions can get long and seem daunting, once you understand them,y ou’ll be applying them fluently. This guide aims to teach you everything you need to know about IFS statements in Google Sheets to get started quickly. Read on to learn more.
What Is the IFS Google Sheets Function?
Google Sheet IFS is known as a conditional function. In this formula, you set some logical tests in a series. There is usually a TRUE value associated with each of the conditions. Google Sheets will perform a logical test on all of the cases. If the first logical test comes as FALSE, then Sheets will check the second case for a match until a match is found. The corresponding value will be displayed when the logical expression returns as TRUE.
This operates like that of nesting numerous IF statements, although it is typically simpler to understand and use. This makes this function perfect when you need to determine a student’s grade based on their performance, compute the commission based on the sales figures, or do any other similar calculations.
IFS Formula Google Sheets
Before we look at the formula in action, let’s look at how the Google Sheet IFS function works. Here is the syntax for the formula:
=IFS(con1, val1, [con2, val2, …])
The formula requires at least two parameters, both of which are required for the IFS statement Google Sheets to work. These parameters are:
- con1: this parameter defines the first condition to be checked by the formula.
- val1: this parameter defines the value which the Google Sheets IFS function will return if the con1 parameter returns as true.
- con2: this parameter is optional and defines the additional conditions you wish to specify on Google Sheet IF multiple conditions.
- val2: the optional value is the value that will be returned if the con2 parameter returns as true. This value becomes a required parameter if a condition parameter is added.
Now that we know the Google Sheets syntax for IFS, let’s look at a few things you should remember when using this formula. These are:
- Only the first TRUE condition’s value would be returned by the IFS function. Consequently, it is possible to have many TRUE conditions. However, just the first one’s value would be returned as output.
- The IFS function’s criteria must all return either TRUE or FALSE. The formula will produce an error if it doesn’t.
- The calculation would produce the #N/A error if every condition in the IFS function returned FALSE. You can use the last Condition as TRUE, and the value as FALSE or a descriptive statement such as “No Match” because the #N/A formula parse error is not particularly helpful in figuring out what occurred.
Google Sheets IFS Formula Examples
Let’s look at some examples of Google Sheets IFS multiple conditions statements.
Example 1 – Logical Test to Calculate Sales Commissions
For this example, we have the sales data for several employees in the company. Suppose you wish to calculate their commission based on their sales. Doing so one by one for every employee can be tedious and time-consuming. Instead, we can use a Google Sheets if statement with multiple conditions to find the commissions for every individual salesperson based on specified data.
Here is how to use the IFS function in Google Sheets:
- In the cell where you wish to add the formula, write an Equal (=) sign to start the formula.
- Enter the keyword for the Google Sheets if multiple conditions formula, which is IFS, and add an opening bracket.
- Here we will start adding the parameters. For this example, we wish to compare the given value with the ones provided in the sales slab. In this case, the first and second conditions will look like B2<E3,F2. This means that if the value in B2 is greater than the value in E3, it will return the value from F2.
- Keep repeating the previous step by adding the parameters to search column E until there is a match.
As we wish to use the formula in several cells, we want some of the values to stay the same when we use the Google Sheets autofill feature. To do this, add a Dollar ($) sign with the cell addresses to make it an absolute reference.
Example 2 – Find Student’s Grade Based on Test Score
In this example, we have the test scores for students in a class, and Google Sheets evaluates and assign them a grade based on their scores. This works in the same way as the previous example.
The formula for this particular example will look like this:
The formula will compare the student’s score with the values in the table towards the right side. Once a match is found, the corresponding grade is displayed.
Frequently Asked Questions
What Can I Use Instead of an IFS Function?
IFS is essentially nested if multiple conditions Google Sheets, so if you wish to use an alternate function, you can utilize the Google Sheets IF function. You can also use the CHOOSE and the VLOOKUP function, but all of these have pros and cons, so make sure to use the one that suits your needs.
What Is the Difference Between IF and IFS in Google Sheets?
You can utilize IF and IFS frequently because they are helpful functions. Sheets IFS verifies more than one condition, whereas IF verifies just one. The IF function allows you to define what value to return if the condition is FALSE, which is the main distinction between it and the IFS function. The IFS function does not allow this natively. When examining several conditions, the IFS formula might be shorter and easier.
The IFS Google Sheets function allows you to test against multiple conditions and return a specified value when TRUE. Once you’ve done this a few times, it will become easy, despite the long formulas.
Google Sheets and Microsoft Excel Expert.