The if function allows you to give a condition to Excel. Based on this condition, many other things can be done. The default output for this function is just true or false. The key here is that you can define an action if the output is true; or another action if the output is false. This ‘action’ can be a simple data output or another calculation or even another function. This lets you create whole flows of logical actions and reactions. Let’s learn with some examples:
IF
The IF function is made up of 3 parts – a condition, true value and a false value. In plain English, the IF function basically tells excel to return an answer based on a condition. It is an “If This – Then This – Otherwise That” statement.
=IF(logical_test, [value_if_true], [value_if_false])
So, the logical test here is the condition that Excel has to evaluate. If the answer to the question is true, Excel will do whatever you put in [value if true]. If the answer is false, excel will do [value if false].
Let’s look at a simple example:
Name 
Age 
Adult? 
Imaran 
24 

Abhithi 
28 

Imtiaz 
8 

Devyani 
19 

Jasamit 
27 

Kunti 
30 

Kalash 
19 

Mangala 
15 

Mayank 
29 

Narayani 
16 

Nischith 
18 

Ojaswini 
30 
Here, we need to find out if each person has crossed the age of 18 or not. Let’s ask Excel using the IF function, so it can fetch correct answers for each row.
Here the IF function would be:
IF(age>=18,”Adult”,”Not Adult”)
IF(age<18,”Not adult”,”Adult”)
Both these options essentially mean the same. Just that the condition is reversed, hence the [value if true] and [value if false] are reversed too.
As soon as we copy the formula down to all cells, the answers come up:
Now, this is just a single condition. But if more conditions are needed to be evaluated to arrive at an answer; then you simply put another IF function in either [the value if true] or in [value if false].
This is demonstrated in the example below where the 2^{nd} IF condition is placed inside the [value if false] of the 1^{st} condition. This is called Nesting of Functions, where you put one function inside the other. Hence this is called, NESTED IF; basically, IF under IF.
We need to identify which category each person falls under, based on the values in the table on the right.
Here, we need to ask multiple questions, one by one:
=IF(question 1, [value if true],question 2, [value if true], … question ‘n’, [value if true], [value if false]…
=IF(age<=12,”Kid”,
IF(age<=19,”Teen”,
IF(age<=21,”Adolescent”,
“Adult”)))
Now, this is how nested if works – it’s a condition inside a condition, inside a condition. If the first condition is true, then the following conditions will be ignored. If the first condition is false, then the next condition is waiting in the [value if false]. Like this, you can ask a series of conditions to get to the correct answer.
The only problem is; as you might already have faced it; these formulas get tougher to read as they get longer and longer. There is a workaround for this – using the IFS function.
IFS
The IFS function is built so it just goes on checking for the first true result and only then calculate. This makes it much faster to implement. Here is how it works:
=IFS(logical_test_1, value_if_true_1,logical_test_2,value_if_true_2…
Using only IF, we had made this formula:
=IF(B2<=12,”Kid”,IF(B2<=19,”Teen”,IF(B2<=21,”Adolescent”,”Adult”)))
But with the IFS function, we don’t need to put multiple IF functions and also not worry about the ( inside a ( inside a ( and closing them all ))) at the end. Here only 1 ( opens and 1 ) closes.
=IFS(B2<=12,”Kid”,B2<=19,”Teen”,B2<=21,”Adolescent”,B2>21,”Adult”)
So, this is easier for us to read and faster for Excel to calculate.
In cases where a value does not match and evaluate to true for any of the conditions, the IFS function will output a #N/A error. Therefore, in cases where a value if false is to be specified, it can be done using the IFERROR function:
=IFERROR(value, value_if_error)
ShrutiMitesh
Interesting and very helpful.
rushabhs
Thank you Shruti 🙂
Aniruddh Amreliya
Time saving
NRG
What version of Excel should I have to use “IFS” function ?
rushabhs
Excel 2016 or Office 365
Naveen
Can we apply it in 2010 version?
rushabhs
No, the IFS function was introduced only in Excel 2016 ( or Office 365)