How to use if function with multiple conditions and outcomes in Excel

This paragraph is original in English.

From my last blog, we have introduced the if function in Excel where following is the syntax:

Syntax:

[vtftable cols=”{0}0:fff2cc;{/}”]
=if(Condition , Result when matched, result when not matched);nn;
[/vtftable]

if function with multiple conditions and outcomes

What if we have multiple conditions? Can we use the if function with multiple conditions?

The answer is “Yes”. There are two ways to to do it.

  • Nest if OR
  • IFS function

Nest if

As mentioned above, the third argument of the “if” statement is the result when not match. In there, you can replace it with another if statement.

[vtftable cols=”{0}0:fff2cc;{/}”]
=if(Condition , Result when matched, if(Condition , Result when matched, result when not matched));nn;
[/vtftable]

IFS function in Excel

“IFS” is an alternative solution since Excel 365. Following is the syntax:

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

For example:

I have the following formulas in B1:

=IFS(A1<10,”0-10″,A1<20,”11-20″,A1>20,”>20″)

Since my value in A1 is “1”, it fullfil the requirement for the first condition (A1<10), it will display “0-10” as the result for B1.

You may also notice that it also matched to the second condition (“A1<20”). However, as soon after it matched to the first condition, it stopped further comparison to other conditions. So it will not display the result as “11-20”

Now my “A1” become 11

Since it matched to the requirement to the second condition, the result for B1 is “11-20”

Leave a Comment