The IFS function in Excel is a logical function that allows you to test multiple conditions and return a value that corresponds to the first TRUE condition. It is similar to the IF function, but allows you to test multiple conditions without the need for nested IF functions.
Syntax:
The syntax of the IFS function is as follows:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], … [logical_test_n, value_if_true_n])
Where:
- logical_test1, logical_test2, … logical_test_n: These are the conditions that you want to test. Each logical test must evaluate to TRUE or FALSE.
- value_if_true1, value_if_true2, … value_if_true_n: These are the values that will be returned if the corresponding logical test is TRUE.
Note that the IFS function can take up to 127 pairs of logical tests and values.
Examples:
Let’s look at some examples to understand how the IFS function works.
Example 1:
Suppose you have a list of students and their grades in a table, and you want to classify their grades as either “Pass” or “Fail” based on the following criteria:
- A grade of 70 or above is a “Pass”
- A grade of below 70 is a “Fail”
You can use the IFS function to classify the grades as follows:
=IFS(A2>=70, “Pass”, A2<70, “Fail”)
In this example, A2 is the cell containing the grade of the first student. The first logical test checks whether the grade is greater than or equal to 70, and the second logical test checks whether the grade is less than 70. If the grade is greater than or equal to 70, the IFS function will return “Pass”; if the grade is less than 70, the IFS function will return “Fail”.
Example 2:
Suppose you have a list of products and their prices, and you want to classify the products as either “Low-priced”, “Medium-priced”, or “High-priced” based on the following criteria:
- A price of $100 or below is “Low-priced”
- A price between $101 and $200 is “Medium-priced”
- A price of $201 or above is “High-priced”
You can use the IFS function to classify the products as follows:
=IFS(B2<=100, “Low-priced”, B2>100 and B2<=200, “Medium-priced”, B2>200, “High-priced”)
In this example, B2 is the cell containing the price of the first product. The first logical test checks whether the price is less than or equal to $100, the second logical test checks whether the price is between $101 and $200, and the third logical test checks whether the price is greater than $200. If the price is less than or equal to $100, the IFS function will return “Low-priced”; if the price is between $101 and $200, the IFS function will return “Medium-priced”; and if the price is greater than $200, the IFS function will return “High-priced”.