The Excel IFS Function
The Excel IFS function checks multiple criteria to return a TRUE condition, the beauty is you can specify what the TRUE value for each condition. It is useful as it avoids nested IF Statements and keeps the formula more manageable.
The following video takes you through this tutorial. The file to go with it is below.
Syntax
The following is the syntax for the IFS Statement.
IFS(Condition Test 1, True Value 2, Condition Test 2, True Value 2, Condition Test 3, True Value 3) etc…
The first True condition is related to the value returned. let’s have a look at a practical example.
In Column C the ratings can be either low, mid or high based on the value in column B. If the low cut off is 50, mid cut off is 75 and the high cut off is 100 then the formula will look as follows.
=IFS( B2<50, "Low", B2<75, "Mid",B2<=100, "High")
The first condition returned that is TRUE will supply the answer. in the first case the value is 62, this is less than 75 and above 50 so this gets assigned Mid, the answer in C3 relates to a value of 47 which is lower than 50 so the first condition is TRUE. The formula works well as there are not multiple nesting of the IF statement.
The following Excel file has the above IFS example.
