How to Use IFS Function in Excel (3 Examples)

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)

Excel IFS Function


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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.

3 Suitable Examples of IFS Function in Excel

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.

Apply IFS Function with Multiple Conditions to Calculate Grades

  • 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.

Apply IFS Function with Multiple Conditions to Calculate Grades

Similar Readings


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.

Utilize IFS Function to Calculate PASS and FAIL of Students in Excel

  • 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.

Use IFS Function with Dates

  • 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.


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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo