IFS Function

One of the many useful functions in Google Sheets is the IFS function. It’s a logical function that allows you to test multiple conditions and return a value based on the first condition that is met. This can save you a lot of time and effort when you have a lot of data and need to check multiple conditions.

In this blog post, we’ll go over how to use the IFS function in Google Sheets, including some examples and tips to help you get the most out of it. So if you’re ready to learn more about the IFS function, let’s dive in.

Definition of IFS Function

The IFS function in Google Sheets is a logical function that allows you to test multiple conditions and return a value based on the first condition that is met. It has the following syntax: IFS(condition1, value_if_true1, condition2, value_if_true2, …, value_if_false). The function tests each condition in order, and if a condition is met, it returns the corresponding value. If none of the conditions are met, the function returns the value specified in the value_if_false argument. The IFS function is useful for simplifying complex formulas and reducing the need for nested IF statements.

Syntax of IFS Function

The syntax of the IFS function in Google Sheets is as follows:

=IFS(condition1, value_if_true1, condition2, value_if_true2, ..., value_if_false)

The function takes one or more pairs of conditions and corresponding values. The first argument, condition1, is a logical expression that is tested. If the condition is true, the function returns the value specified in value_if_true1. If the condition is false, the function moves on to the next pair of condition and value arguments and tests the second condition. This process continues until a condition is met or until the function reaches the value_if_false argument. If none of the conditions are met, the function returns the value specified in value_if_false.

For example, you might use the IFS function to test whether a cell value is greater than 10, equal to 10, or less than 10, and return a different value based on the result of the test. You could do this using the following formula:

=IFS(A1 > 10, "Greater than 10", A1 = 10, "Equal to 10", A1 < 10, "Less than 10", "Error")

In this example, if the value in cell A1 is greater than 10, the function will return “Greater than 10”. If the value in cell A1 is equal to 10, the function will return “Equal to 10”. If the value in cell A1 is less than 10, the function will return “Less than 10”. If none of these conditions are met, the function will return “Error”.

Examples of IFS Function

1. Test for multiple ranges and return a corresponding text value:
   Suppose you have a list of numbers in a column and you want to classify them as “small,” “medium,” or “large” based on their value. You could use the IFS function to test for multiple ranges and return a corresponding text value.
   For example, to classify numbers less than 10 as “small,” numbers between 10 and 20 as “medium,” and numbers greater than 20 as “large,” you could use the following formula:

   =IFS(A1 < 10, "Small", A1 >= 10 and A1 < 20, "Medium", A1 >= 20, "Large")

   This formula tests the value in cell A1. If the value is less than 10, the function returns “Small”. If the value is greater than or equal to 10 but less than 20, the function returns “Medium”. If the value is greater than or equal to 20, the function returns “Large”.

2. Test for multiple conditions and return a corresponding numerical value:
   You can also use the IFS function to test for multiple conditions and return a corresponding numerical value. For example, suppose you have a list of names in a column and you want to assign a score to each name based on the first letter of the name. You could use the IFS function to test for different letters and return a corresponding score.
   For example, to assign a score of 1 to names that start with A, B, or C; a score of 2 to names that start with D, E, or F; and a score of 3 to names that start with G, H, or I, you could use the following formula:

   =IFS(LEFT(A1) = "A", 1, LEFT(A1) = "B", 1, LEFT(A1) = "C", 1, LEFT(A1) = "D", 2, LEFT(A1) = "E", 2, LEFT(A1) = "F", 2, LEFT(A1) = "G", 3, LEFT(A1) = "H", 3, LEFT(A1) = "I", 3)

   This formula uses the LEFT function to extract the first letter of the name in cell A1, and then tests for each letter using the IFS function. If the first letter of the name is “A,” “B,” or “C,” the function returns 1. If the first letter of the name is “D,” “E,” or “F,” the function returns 2. If the first letter of the name is “G,” “H,” or “I,” the function returns 3.

3. Test for multiple conditions and return a corresponding cell reference:
   You can also use the IFS function to test for multiple conditions and return a corresponding cell reference. For example, suppose you have a list of names in one column and corresponding values in another column, and you want to look up the value for a given name. You could use the IFS function to test for different names and return the corresponding value.
   For example, to return the value for the name “John” from column B, the value for the name “Mary” from column C, and the value for the name “Paul” from column D, you could use the following formula:

   =IFS(A1 = "John", B1, A1 = "Mary", C1, A1 = "Paul", D1)

   This formula tests the value in cell A1. If the value is “John,” the function returns.

Use Case of IFS Function

Grade calculation:
Suppose you are a teacher and you have a spreadsheet with a list of students and their test scores. You want to calculate their grades based on the following criteria:

• 90 or above: A
• 80 to 89: B
• 70 to 79: C
• 60 to 69: D
• Below 60: F

You can use the IFS function to test for multiple ranges and return a corresponding grade. For example, to calculate the grade for the score in cell A1, you could use the following formula:

=IFS(A1 >= 90, "A", A1 >= 80, "B", A1 >= 70, "C", A1 >= 60, "D", "F")

This formula tests the value in cell A1. If the value is 90 or above, the function returns “A”. If the value is between 80 and 89, the function returns “B”. If the value is between 70 and 79, the function returns “C”. If the value is between 60 and 69, the function returns “D”. If the value is below 60, the function returns “F”.

Sales commission calculation:

Suppose you work in sales and you have a spreadsheet with a list of salespeople and their total sales for the month. You want to calculate their commission based on the following criteria:

• Sales between $0 and $5,000: 5% commission
• Sales between $5,001 and $10,000: 7.5% commission
• Sales above $10,000: 10% commission

You can use the IFS function to test for multiple ranges and return a corresponding commission percentage. For example, to calculate the commission for the sales in cell A1, you could use the following formula:

=IFS(A1 >= 0 and A1 <= 5000, A1 * 5%, A1 > 5000 and A1 <= 10000, A1 * 7.5%, A1 > 10000, A1 * 10%)

This formula tests the value in cell A1. If the value is between $0 and $5,000, the function calculates the commission as 5% of the sales. If the value is between $5,001 and $10,000, the function calculates the commission as 7.5% of the sales. If the value is above $10,000, the function calculates the commission as 10% of the sales.

Expense classification:

Suppose you have a spreadsheet with a list of expenses and you want to classify each expense as “essential,” “optional,” or “luxury” based on the amount. You can use the IFS function to test for multiple ranges and return a corresponding classification.

For example, to classify expenses less than $100 as “essential,” expenses between $100 and $500 as “optional,” and expenses above $500 as “luxury,” you could use the following formula:

=IFS(A1 < 100, "Essential", A1 >= 100 and A1 < 500, "Optional", A1 >= 500, "Luxury")

This formula tests the value in cell A1. If the value is less than $100, the function returns “Essential”. If the value is between $100 and $500, the function returns “Optional”. If the value is above $500, the function returns “Luxury”.

Limitations of IFS Function

The IFS function in Google Sheets has a few limitations that you should be aware of:

1. The function can only test for up to 127 conditions: The IFS function can only test for up to 127 pairs of conditions and values. If you need to test for more than 127 conditions, you will need to use nested IF statements or another function such as the SWITCH function.
2. The function can only return a single value: The IFS function can only return a single value, regardless of how many conditions are met. If you need to return multiple values based on multiple conditions, you will need to use another function such as the INDEX and MATCH functions.
3. The function does not support cell references for the value_if_true or value_if_false arguments: You cannot use cell references for the value_if_true or value_if_false arguments in the IFS function. If you need to use cell references as part of your value, you will need to use a different function such as the INDEX and MATCH functions.
4. The function does not support array formulas: The IFS function does not support array formulas, which means you cannot use it to return multiple results for multiple cells at once. If you need to apply the same formula to multiple cells, you will need to enter the formula separately for each cell or use a different function such as the INDEX and MATCH functions.
5. The function is not available in all versions of Google Sheets: The IFS function is not available in all versions of Google Sheets. If you are using an older version of the software, you may need to upgrade to a newer version in order to use this function.

Commonly Used Functions Along With IFS

Here are some commonly used functions that are often used along with the IFS function in Google Sheets:

1. AND: The AND function is a logical function that allows you to test multiple conditions at the same time. It has the following syntax: AND(logical1, logical2, …). The function returns TRUE if all of the conditions are met, and FALSE if any of the conditions are not met. For example, to test whether the value in cell A1 is greater than 10 and the value in cell B1 is less than 20, you could use the following formula:

   =AND(A1 > 10, B1 < 20)

   This formula tests both conditions and returns TRUE if both are met, and FALSE if either is not met.
   You can use the AND function inside the IFS function to test multiple conditions at the same time. For example, to test whether the value in cell A1 is greater than 10 and less than 20, and return “Yes” if the condition is met and “No” if it is not met, you could use the following formula:

   =IFS(AND(A1 > 10, A1 < 20), "Yes", "No")

2. OR: The OR function is a logical function that allows you to test multiple conditions and return TRUE if any of the conditions are met. It has the following syntax: OR(logical1, logical2, …). The function returns TRUE if any of the conditions are met, and FALSE if none of the conditions are met. For example, to test whether the value in cell A1 is greater than 10 or the value in cell B1 is less than 20, you could use the following formula:

   =OR(A1 > 10, B1 < 20)

   This formula tests both conditions and returns TRUE if either is met, and FALSE if both are not met.
   You can use the OR function inside the IFS function to test multiple conditions and return a value if any of the conditions are met. For example, to test whether the value in cell A1 is greater than 10 or less than 20, and return “Yes” if either condition is met and “No” if neither is met, you could use the following formula:

   =IFS(OR(A1 > 10, A1 < 20), "Yes", "No")

3. LEFT: The LEFT function is a text function that allows you to extract a specified number of characters from the left side of a string of text. It has the following syntax: LEFT(text, number_of_characters). The function returns the specified number of characters from the left side of the text string.
   For example, to extract the first 3 characters from the text in cell A1, you could use the following formula:

   =LEFT(A1, 3)

   This formula returns the first 3 characters from the text in cell A1.
   You can use the LEFT function inside the IFS function to test for specific characters in a text string. For example, to test whether the first character of the text in cell A1 is “A,” “B,” or “C,” and return “Yes” if the condition is met and “No” if it is not met, you could use the following formula:

   =IFS(LEFT(A1) = "A", "Yes", LEFT(A1) = "B", "Yes", LEFT(A1) = "C", "Yes", "No")

Summary

The IFS function in Google Sheets is a powerful and versatile tool for testing multiple conditions and returning a value based on the first condition that is met. It can save you a lot of time and effort when you have a lot of data and need to check multiple conditions. The function has a simple syntax and is easy to use, making it a great choice for a wide range of applications.

Some key points to remember about the IFS function include:

• The function can test for up to 127 conditions.
• The function can only return a single value.
• The function does not support cell references for the value_if_true or value_if_false arguments.
• The function does not support array formulas.
• The function may not be available in all versions of Google Sheets.

If you are new to the IFS function, we encourage you to try using it in your own Google Sheets to see how it can help you simplify complex formulas and streamline your data manipulation tasks. With a little practice, you’ll be a pro at using the IFS function in no time!