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.
- 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.
Below is the formula that will give you the commission value for each sales personnel:
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.
Below is the formula that will give you the grades for each student:
You May Also Like the Following Google Sheets Tutorials:
- Using Query Function in Google Sheets.
- The Ultimate Guide to Google Sheets VLOOKUP Function.
- Using IFERROR Function in Google Sheets.
- How to Use COUNTIF Function in Google Sheets.
- Using IMPORTDATA Function in Google Sheets.
- Using OR Function in Google Sheets.