PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

EXCEL FUNCTIONS-IFS

INTRODUCTION

IFS function comes under the LOGICAL FUNCTIONS category in Excel.

IFS function is very important and is going to be used a lot which makes it one of the very important functions in excel.

THE IFS FUNCTION CHECKS THE DIFFERENT GIVEN CONDITIONS AND RETURNS THE CORRESPONDING VALUE OF THE FIRST TRUE CONDITION.

This function is an extension to the very useful IF FUNCTION.

In this article, we’ll learn about the purpose, formula syntax and examples of the IFS FUNCTION IN EXCEL.

PURPOSE OF IFS IN EXCEL

IFS FUNCTION is simply an extension to the very useful IF FUNCTION.

IFS Function lets us insert a number of conditions and the associated values if that particular condition is true.

If you have been using the EXCEL for a while, you must have used the NESTED IF FUNCTION. IFS function is a dedicated function for the NESTED IF which is more readable and easy to implement.

For the first time users, let us try to understand the IFS FUNCTION.

Suppose we have different codes for the fruits. Such as

1 – Apple

2 – Banana

3 – Pine Apple

4 – WaterMelon

and so on.

We need to put the condition to check the name of the fruit from the given code using the IFS FUNCTION.

We would put the function as

IFS( ” If code = 1″, Return Apple, ” If code =2 ” Return Banana , If code = 3…. and so on.

We’ll learn the proper formula in the examples below.

Simply stating, we’ll use IFS when we need to put a number of conditions in the same statement or simply speaking it is a MULTIPLE IF.

PREREQUISITES TO LEARN IFS

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

Basic understanding of how to use a formula or function.
Basic understanding of rows and columns in Excel.
The knowledge of IF function will be of great help.
Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

SYNTAX: IFS FUNCTION

The Syntax for the IFS function is

=IF(TEST CONDITION 1, VALUE IF CONDITION 1 IS TRUE, TEST CONDITION 2, VALUE IF CONDITION 2… )

TEST CONDITION is the test condition which you want to test. The condition must return a TRUE OR FALSE.

VALUE IF CONDITION IS TRUE is the value which will be returned by the function if the corresponding condition is true.

We can enter up to 127 conditions.

If more than two conditions are true, the leftmost condition will be executed and value will be returned.

EXAMPLE:IFS FUNCTION IN EXCEL

DATA SAMPLE

For the better understanding of the use of IFS FUNCTION, let us take an example.

We have SPECIAL CODES for a few fruits which are given below.

We want Excel to find out the fruit on the basis of selected code.

We’ll create a simple drop down list and the output cell will show the fruit chosen.

CREATING A DROPDOWN LIST FOR THE SELECTION OF CODE.

STEPS TO CREATE A DROP DOWN LIST:

*the drop down list creation steps are brief as there is a complete article for the same. [ CLICK HERE TO VISIT]

Select the cell in which we want to insert the data list.
Go to DATA TAB and choose DATA VALIDATION>DATA VALIDATION [ UNDER DROP DOWNLIST ].
Select the Options as
ALLOW: CHOOSE LIST FROM THE DROP DOWN.
SOURCE: THE SOURCE OF THE LIST DATA. FOR OUR EXAMPLE, THE CODES ARE AVAILABLE AT A4 TO A8 , HENCE THE RANGE $A$4:$A$8.

Click OK.
The dropdown list will be created as shown in the picture below.

Now , the list is already created. Now we want to put the formula using the function IFS to find out the fruit code immediately after selecting the fruit code.

SOLUTION:

STEPS TO USE IFS FUNCTION TO CHOOSE THE FRUIT ON THE BASIS OF FRUIT CODE.

Select the cell where we want the result.
Put the formula as = IFS (” FIRST CONDITION:, VALUE IF FIRST CONDITION IS TRUE, SECOND CONDITION, VALUE IF SECOND CONDITION IS TRUE , AND SO ON….).
For our example, the code is present in the cell G3, so the formula become =IFS(G3=1,”APPLE”,G3=2,”BANANA”,G3=3,”PINEAPPLE”,G3=4,”WATERMELON”,G3=5,”PEACH”)
Click OK.
We are all set to test our example.
The following picture shows the formula used.

*EXPLANATION FOLLOWS THE PICTURE BELOW.

STEPS TO USE IFS FUNCTION TO CHOOSE THE FRUIT ON THE BASIS OF FRUIT CODE.

Select the cell where we want the result.
Put the formula as = IFS (” FIRST CONDITION:, VALUE IF FIRST CONDITION IS TRUE, SECOND CONDITION, VALUE IF SECOND CONDITION IS TRUE , AND SO ON….).
For our example, the code is present in the cell G3, so the formula become =IFS(G3=1,”APPLE”,G3=2,”BANANA”,G3=3,”PINEAPPLE”,G3=4,”WATERMELON”,G3=5,”PEACH”)
Click OK.
We are all set to test our example.
The following picture shows the formula used.

*EXPLANATION FOLLOWS THE PICTURE BELOW.

EXPLANATION:

Let us try to understand the steps which we followed in the discussed example.

The CODE SELECTION will take place in the cell G3 which means that the condition will be applied on the cell G3.

We used the formula as =IFS(G3=1,”APPLE”,G3=2,”BANANA”,G3=3,”PINEAPPLE”,G3=4,”WATERMELON”,G3=5,”PEACH”)

The function starts with the FUNCTION NAME IFS.

The first argument is the first condition. We want to know if the code is 1 so we put the argument as G3=1, which will be the instruction to EXCEL to check if G3 is equal to 1 or not, if YES, the corresponding value i.e. second argument will be returned which is APPLE.

If not, then next condition will be tested, which is third argument where we again check if G3=2 or not, if TRUE, fourth argument will be returned as value which is BANANA and so on.

Similarly , all the conditions will be checked i.e. codes from 1 to 5.

ONE THING TO BE NOTICED HERE IS THAT WE HAVE USED A LIST, SO THERE ARE NO CHANCES FOR ANY WRONG CODE.

The following picture shows the running of the example.

TESTING THE EXAMPLE:

KNOWLEDGE BYTES

NESTED IF VS IFS

Nested IF and IFS do the same job but a few differences can be noticed.

Nested If is used by nesting the IF function one inside the another [ The process known as nesting ]. whereas IFS is the dedicated Function made for the IF Nesting.
IFS is a new function which is available only in the latest Excel version past 2019 only.
IFS is more readable whereas Nested IF is somewhat harder to understand.
Conclusion is that if we have IFS, we should use IFS in place of Nested IF because of its simplicity and readability in case of troubleshooting.

*Although we can have 127 arguments in the IFS function, never use a high number of IF conditions otherwise troubleshooting is going to be tough.

CONTENTS