The IFS function in excel will be used where you have two or more conditions to apply on the selected array. You will get to know about that in this article, what is IFS function and how you can apply it on two or more criteria or conditions.

Contents

IFS Function in Excel: –

The feature of the “IFS” function: –

The logical operator: –

Explanation of IFS Function:-

Example of IFS Function: –

Solution:

Download Practice workbook:

IFS Function in Excel: –

The “IFS” Function in Excel is a logical test of two or more logical conditions, representing the result in continue testing way. It means if the first logical condition is true, it will show the result as you desire, but if the logical condition is false, it will test the second condition of the formula. We can use this formula where we want to apply the condition of less than, greater than, and equal to. It means if the selected cell value’s value is less than a specific amount, then answer the specific true value(it may be in numeric or in words) rather than check the next criteria or condition, and so on.

The feature of the “IFS” function: –

With the help of the IFS Function, we can retrieve the data if the condition is qualified on the selected cell value.
VLOOKUP and HLOOKUP shows only matched value but the “IFS” function shows the value in both ways.
It helps in applying a particular condition to the selected cell value.

The logical operator: –

We can use the “IF” function only to follow a logical test on the selected cell(array).

Logical Test	Meaning
=	Equal to
<>	Not Equal to
>	Greater than
>=	Greater than and Equal to
<	Less than
<=	Less than and equal to

Explanation of IFS Function:-

Now, We will explain the Arguments of the Function.

=IF(logical_test1, value_if_true1, [logical_test2, value_if_true2]………)

logical_test1:- The Logical test1 is the value or condition which will provide us with the result of the selected cell as true or false. It is a specific condition/test as explained in the above table.

value_if_true1: – The value if true1 is that value which you want to show if the condition or logical test is true.

logical_test2:- The Logical test2 is the value or condition which will provide us with the result of the selected cell as true or false. It is a specific condition/test as explained in the above table.

value_if_true2: – The value if true2 is that value which you want to show if the condition or logical test is true.

logical_test(n):- The Logical test(n) is the value or condition which will provide us with the result of the selected cell as true or false. It is a specific condition/test as explained in the above table.

value_if_true(n): – The value if true(n) is that value which you want to show if the condition or logical test is true.

Example of IFS Function: –

In the following table, we want to retrieve the result of all students in the grades. The table of grades is given in the workbook.

Solution:

We will Apply the if function and get the result. This is shown in the following steps: –

Step No. 1:-

We will write the “=ifs( ” in the column of the result

Step No. 2

Now create the logical test 1 by selecting a cell or value and then apply the condition to it. Like I am selecting E5(the column containing Marks obtained) and write condition <50.

Example of Ifs Function - Solution Step no.2.1

Or instead of writing the value of the condition, you can select the value of the cell which contains the value of the condition. As shown in the below image. I am selecting the H6. The full logical test becomes “E5<h6”.

Example of Ifs Function - Solution Step no.2

Step No. 3: –

Now please fix the cell so you don’t face problems while copy that formula in the below cells. Fix it by adding the $ sign before the name of column and row.

I have fixed that cell (i.e.$H$6) that contained the marks range which will help me to get the result of the grade. The marks range will remain the same for all students. So, the same condition will be applied to all. I don’t fix the cell which contained the value of marks obtained (i.e. E5) because we have to change it for all students and we have to select the marks obtained by each student.

Example of IFS Function in Excel - Solution Step no.3 — Example of IFS Function in Excel – Solution Step no.3

Step No. 4: –

Now, select or write the answer which you want to show if your already selected condition or logical test becomes true. I have selected the cell which contained the value of grade which I want to show when the condition or logical test becomes true shown as below:

Step No. 5: –

Now please fix the cell so you don’t face problems while copy that formula in the below cells. Fix it by adding the $ sign before the name of column and row ($J$5)as shown below:

Step No. 6: –

Now you can create the logical test 2 by selecting a cell that contains a value or write value and then apply the condition to it.

This logical test will be performed by excel only when the 1^st logical test got false.

So, please apply that condition or logical test first which you want to qualify first by the selected value.

Now I have selected the next numbers for the next higher grade and fix selected cell as shown below:

Example of Ifs Function - Solution Step no.6 — Example of IFS Function in Excel – Solution Step no.6

Step No. 7: –

Now, select or write the answer which you want to show if your already selected condition 2 or logical test 2 becomes true. I have selected the cell and fixed it which contained the next grade which I want to show when condition 2 or logical test 2 becomes true shown as below:

Step No. 8: –

Now, you can create the logical test 3 and so on. You can apply many logical tests in a single formula. I have created the 7 Logical Test or condition. These all conditions give me the exact result which I want to show.

The process of creating a logical test remains the same as above we have discussed. So now I have created all the Logical test in the single image shown below:

Example of Ifs Function - Solution Step no.8 — Example of IFS Function in Excel – Solution Step no.8

Step No. 9: –

Now, Close the Brackets and complete the formula as shown below:

Step No. 10: –

Now, You can check the return value is correct or not. The condition is if the value of marks obtained is between 50 to 59 then the show the grade E. I have highlighted with a red border and red text in the table of Grades according to scores.

So the value we got is “E” which is correct means our formula works correctly. The result value is also highlighted with a red border and red text in the table of marks obtained by students.

Example of Ifs Function - Solution Step no.10 — Example of IFS Function in Excel – Solution Step no.10

Step No. 11: –

Now, In the end, I have copied the formula and paste it on all cell which is given in the question. So we got the grade of each student according to the conditions shown as below:

Download Practice workbook:

Please Download Practice workbooks from the below links to know that how much you have understood the IFS function in Excel:

This is all about the IFS function in excel.

Thanks, If you have any confusion in this formula please write a comment in the comment box we reply to you as soon as possible.

Please Share it with your friends and family.

also, write your feedback in the comment box.

to buy Microsoft Excel Click Here.

Also, Check out the following function of excel: –

Archives

Categories

Meta

IFS Function in Excel – Free 2 Practice workbooks

IFS Function in Excel: –

The feature of the “IFS” function: –

The logical operator: –

Explanation of IFS Function:-

Example of IFS Function: –

Solution:

Step No. 1:-

Step No. 2

Step No. 3: –

Step No. 4: –

Step No. 5: –

Step No. 6: –

Step No. 7: –

Step No. 8: –

Step No. 9: –

Step No. 10: –

Step No. 11: –

Download Practice workbook: