While working in Microsoft Excel, there are various formulas to make our work easier. The IFS formula is one of them. It has a wide range of applications in Excel. The IFS function performs a logical test. It returns one value if the result is TRUE, and another if the result is FALSE. In this article, we will illustrate how to use the IFS function in Excel. To do this, we will go over several examples.
IFS Function of Excel (Quick View)
Introduction to IFS Function in Excel
Function Objective
- The IFS function takes multiple conditions and values and returns the corresponding value to the first TRUE
- It has both the Non-Array form and Array Which means each of its arguments can be a single value or an array of values.
Syntax
The syntax of the IFS function is:
=IFS(logical_test1,value_if_true1,[logical_test2],[value_if_true2]...)Arguments Explanation
| Arguments | Required/Optional | Explanation |
|---|---|---|
| logical_test1 | Required | The first condition (TRUE or FALSE) |
| value_if_true1 | Required | Value to be returned if the first condition is TRUE |
| logical_test2 | Optional | The second condition (TRUE or FALSE) |
| value_if_true2 | Optional | Value to be returned if the second condition is TRUE |
| … | … | … |
| … | … | … |
Return Value
- It returns the value associated with the first condition that is satisfied.
- This means, if logical_test2, logical_test_3, and many more conditions are satisfied, it will only return the value_if_true2 argument.
Notes:
- Enter the arguments in pairs. For example, if you enter the argument logical_test_2, you must enter the argument value_if_true2, though it is optional. Otherwise, the function will not work.
- You can enter up to 127 conditions within the IFS
- The IFS function also works for Array In place of entering a single value, you can enter an Array of values for each of the arguments.
- When more than one condition is satisfied, the IFS function only returns the value that is associated with the first condition that is satisfied.
How to Use IFS Function in Excel: 3 Suitable Examples
1. Apply IFS Function with Multiple Conditions to Calculate Grades
Now we shall use the IFS function to calculate the grades of some students with multiple conditions in a school. We have the names of some students and their marks in mathematics at a school named Glory Kindergarten. When the mark is greater than or equal to 80, the grade is A; when it is greater than or equal to 70, it is B, when it is greater than or equal to 60, it is C; and when it is less than 60, it is Fail. Let’s follow the instructions below to learn!
Steps:
- First of all, select cell D5 and write down the below IFS function in that cell. The function is,
=IFS(C5:C9>=80,"A",C5:C9>=70,"B",C5:C9>=60,"C",TRUE,"F")Formula Breakdown:
IFS(C4>=80,"A",C4>=70,"B",C4>=60,"C",TRUE,FAIL) first checks whether the mark in cell C4 is greater than or equal to 80 or not.
- If yes, it returns A.
- If not, it checks whether it is greater than or equal to 70 or not.
- If yes, it returns B.
- If not, it checks whether it is greater than or equal to 60 or not.
- If yes, it returns C.
- If not, then it returns F.
- Hence, simply press Enter on your keyboard. As the IFS function is a dynamic function, you will be able to determine the grade of each student which has been given in the below screenshot.
2. Utilize IFS Function to Calculate PASS and FAIL of Students in Excel
Instead of having marks in only mathematics, we now have marks in mathematics, physics, and chemistry. Now we will determine for all the students whether he/she did pass the examination or not. Remember, to pass the examination, one has to pass all three subjects. But failing one subject is enough to fail the whole examination. And to pass one subject, one needs at least 60 marks. Let’s follow the instructions below to learn!
Steps:
- First of all, select cell D5 and write down the below IFS function in that cell. The formula is,
=IFS(C6:C10<60,"FAIL",D6:D10<60,"FAIL",E6:E10<60,"FAIL",TRUE,"PASS")Formula Breakdown:
IFS(C4<60,"FAIL",D4<60,"FAIL",E4<60,"FAIL",TRUE,"PASS") first checks whether the mark in cell C4 (Mathematics) is less than 60 or not.
- If yes, it returns FAIL.
- If not, it checks whether the Cell D4 (Physics) mark is less than 60 or not.
- If yes, it returns FAIL.
- If not, it checks whether the Cell E4 (Chemistry) mark is less than 60 or not.
- If yes, it returns FAIL.
- If not, it returns PASS.
- Hence, simply press Enter on your keyboard. As the IFS function is a dynamic function, you will be able to determine the Pass or Fail of each student which has been given in the below screenshot.
3. Use IFS Function with Dates
Now, we will check the status (he/she is Permanent, Qualified or on Probation) of an employee of XYZ company using the IFS function in terms of dates. This is an easy and time-saving task as well. Let’s follow the instructions below to learn!
Steps:
- First of all, select cell D5 and write down the below IFS function in that cell. The function is,
=IFS(E5:E9>=3000,"Permanent",E5:E9>=2000,"Qualified",E5:E9>=500,"Probationary")Formula Breakdown:
=IFS(E5:E9>=3000,"Permanent",E5:E9>=2000,"Qualified",E5:E9>=500,"Probationary") first checks whether the mark in cell C4 is greater than or equal to 3000 or not.
- If yes, it returns Permanent.
- If not, it checks whether it is greater than or equal to 2000 or not.
- If yes, it returns Qualified.
- If not, it checks whether it is greater than or equal to 500 or not.
- If yes, it returns Probationary.
- Hence, simply press Enter on your keyboard. As the IFS function is a dynamic function, you will be able to determine the status of each employee which has been given in the below screenshot.
Notes: Excel IFS Function Not Available
- The IFS function is only available in Excel 2019 and later versions and Office 365 as well.
Common Errors with IFS Function
#N/A error occurs when all the conditions within the IFS function are FALSE.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
Thus, you can use the IFS function of Excel to check multiple numbers of conditions simultaneously. Do you have any questions? Feel free to inform us.
<< Go Back to Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
