How to Use IFS Function in Excel (2 Examples)

Overview of IFS Function of Excel

Today I will be showing how you can use the IFS function of Excel to check multiple conditions together.


IFS Function of Excel (Quick View)

Overview of IFS Function of Excel


Download Practice Workbook


Excel IFS Function: Syntax and Arguments


Summary

  • The IFS function takes multiple conditions and values and returns the corresponding value to the first TRUE condition.
  • It has both the Non-Array form and Array form. That means each of its arguments can be a single value or an array of values.
  • The IFS function is only available from Excel 2019 and in Office 365.

Syntax

Syntax of IFS Function

The syntax of the IFS function is:

=IFS(logical_test1,value_if_true1,[logical_test2],[value_if_true2]...)

Arguments

Argument Required or Optional Value
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

Note:

  • 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 function.
  • The IFS function also works for Array arguments. In place of entering a single value, you can enter an Array of values for each of the arguments.

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

  • When more than one condition is satisfied, the IFS function only returns the value that is associated with the first condition that is satisfied.

IFS Function in Excel

In the figure, the value 60 satisfies the second and the third condition, “>50” and “>30”. But only the value associated with the second condition is returned, “B”

  • If you want, you can enter an array of conditions as an argument.

Array Formula of IFS

Here we have entered three arrays of conditions as logical_test1, logical_test2 and logical_test3 respectively.

logical_test1 value_1 logical_test2 value_2 logical_test3 value_3
B5>70 A B7>50 B B6>30 C
B6>70 A B8>50 B
B7>70 A B9>50 B
B8>70 A
B9>70 A

Now if you break down the array formula we will get 5 single formulas, like this:

  1. IFS(B5>70,”A”,B7>50,”B”,B6>30,”C”)
  2. IFS(B6>70,”A”,B8>50,”B”)
  3. IFS(B7>70,”A”,B9>50,”B”)
  4. IFS(B8>70,”A”)
  5. IFS(B9>70,”A”)

The return values are B, B, B, A, A respectively.

  • If none of the conditions is satisfied, IFS will throw a “#N/A” error. To avoid that you can enter a TRUE as the last condition and a value after that which will be returned if no condition is met.

IFS Formula in Excel

In the image, neither of the three conditions “>100”, “>90” and “>80” are met. Then it met the fourth condition TRUE and returned what is after it, “Not Valid”.

Thus you can specify a condition that will be returned if all the conditions are FALSE.


Excel IFS Function: 2 Examples


1. Calculating Grades of Students

Now we shall use the IFS function to calculate the grades of some students in a school.

Look at the data set below.

We have the names of some students and their marks in Mathematics of a school named Glory Kindergarten.

A Data Set in Excel

Now we will determine the grade of each student.

Consider 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 60, it is C, and when it is less than 60, it is Fail.

To calculate the grade of all the students, we can enter this formula in the first cell:

=IFS(C4>=80,"A",C4>=70,"B",C4>=60,"C",TRUE,FAIL)

And then drag the Fill Handle.

Or enter this Array Formula in the first cell:

=IFS(C4:C8>=80,"A",C4:C8>=70,"B",C4:C8>=60,"C",TRUE,"FAIL")

Formula to Find Grades Using IFS

Either one you use, you will get the grades of all the students.

Explanation of the Formula

Whether you drag the Fill Handle or enter the Array Formula, both are the same actually. We are using 5 formulas together.

  • IFS(C4>=80,"A",C4>=70,"B",C4>=60,"C",TRUE,FAIL)
  • IFS(C5>=80,"A",C5>=70,"B",C5>=60,"C",TRUE,FAIL)
  • IFS(C6>=80,"A",C6>=70,"B",C6>=60,"C",TRUE,FAIL)
  • IFS(C7>=80,"A",C7>=70,"B",C7>=60,"C",TRUE,FAIL)
  • IFS(C8>=80,"A",C8>=70,"B",C8>=60,"C",TRUE,FAIL)

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

Same for the rest of the four formulas.

Formula Output Explanation
=IFS(C4>=80,”A”,C4>=70,”B”,C4>=60,”C”,TRUE,FAIL) C Returns A if the mark in C4 is greater than or equal to 80, B if is greater than or equal to 70, C if it is greater than or equal to 60, and FAIL otherwise.
=IFS(C5>=80,”A”,C5>=70,”B”,C5>=60,”C”,TRUE,FAIL) FAIL Returns A if the mark in C5 is greater than or equal to 80, B if is greater than or equal to 70, C if it is greater than or equal to 60, and FAIL otherwise.
=IFS(C6>=80,”A”,C6>=70,”B”,C6>=60,”C”,TRUE,FAIL) C Returns A if the mark in C6 is greater than or equal to 80, B if is greater than or equal to 70, C if it is greater than or equal to 60, and FAIL otherwise.
=IFS(C7>=80,”A”,C7>=70,”B”,C7>=60,”C”,TRUE,FAIL) B Returns A if the mark in C7 is greater than or equal to 80, B if is greater than or equal to 70, C if it is greater than or equal to 60, and FAIL otherwise.
=IFS(C8>=80,”A”,C8>=70,”B”,C8>=60,”C”,TRUE,FAIL) A Returns A if the mark in C8 is greater than or equal to 80, B if is greater than or equal to 70, C if it is greater than or equal to 60, and FAIL otherwise.

2. Calculating PASS and FAIL of Students

Now instead of having marks in only Mathematics, we have the marks in Mathematics, Physics and Chemistry.

Data Set in Excel

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 in all three subjects. But failing in one subject is enough to fail in the whole examination.

And to pass in one subject, one needs at least 60 marks.

To determine whether a student has passed or not, enter this formula in the first cell:

=IFS(C4<60,"FAIL",D4<60,"FAIL",E4<60,"FAIL",TRUE,"PASS")

And then drag the Fill Handle.

Or enter this Array Formula in the first cell:

=IFS(C4:C8<60,"FAIL",D4:D8<60,"FAIL",E4:E8<60,"FAIL",TRUE,"PASS")

Formula to Find Pass or Fail Using IFS

Whether you drag the Fill Handle or enter the Array Formula, both are the same actually. We are using five formulas together.

  • IFS(C4<60,"FAIL",D4<60,"FAIL",E4<60,"FAIL",TRUE,"PASS")
  • IFS(C5<60,"FAIL",D5<60,"FAIL",E5<60,"FAIL",TRUE,"PASS")
  • IFS(C6<60,"FAIL",D6<60,"FAIL",E6<60,"FAIL",TRUE,"PASS")
  • IFS(C7<60,"FAIL",D7<60,"FAIL",E7<60,"FAIL",TRUE,"PASS")
  • IFS(C8<60,"FAIL",D8<60,"FAIL",E8<60,"FAIL",TRUE,"PASS")

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.

Same for the rest of the four formulas.

Formula Output Explanation
=IFS(C4<60,”FAIL”,D4<60,”FAIL”,E4<60,”FAIL”,TRUE,”PASS”) FAIL Returns PASS if the marks in all the cells C4, D4 and E4 are greater than 60, FAIL otherwise.
=IFS(C5<60,”FAIL”,D5<60,”FAIL”,E5<60,”FAIL”,TRUE,”PASS”) FAIL Returns PASS if the marks in all the cells C5, D5 and E5 are greater than 60, FAIL otherwise.
=IFS(C6<60,”FAIL”,D6<60,”FAIL”,E6<60,”FAIL”,TRUE,”PASS”) PASS Returns PASS if the marks in all the cells C6, D6 and E6 are greater than 60, FAIL otherwise.
=IFS(C7<60,”FAIL”,D7<60,”FAIL”,E7<60,”FAIL”,TRUE,”PASS”) PASS Returns PASS if the marks in all the cells C7, D7 and E7 are greater than 60, FAIL otherwise.
=IFS(C8<60,”FAIL”,D8<60,”FAIL”,E8<60,”FAIL”,TRUE,”PASS”) FAIL Returns PASS if the marks in all the cells C8, D8 and E8 are greater than 60, FAIL otherwise.

Common Errors with IFS Function

Error When They Show
#N/A Shows when all the conditions within the 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.


Further Readings

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