The Excel IFS function is a new excel logical formula that is available in Excel 2016 version and above. This function checks for multiple conditions in Excel at once and returns the result based on the condition fulfillment.
The IFS formula in Excel is only available in the Windows version of Excel 2016 and is not yet available for Mac OS. This function works as a replacement of the complex nested IF function.
In this blog, we would deep dive to learn when to use the IFS excel formula, the function’s syntax and arguments, and its examples.
Purpose of Excel IFS Formula
The main objective of the =IFS() formula in excel to check for multiple conditions at one go in a sequence. Based on this, the formula returns the stated value, as soon as the condition fulfills first.
There are many practical scenarios wherein the IFS formula of Excel is used. We would cover some of the practical scenarios later in the examples.
Syntax and Arguments
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ……)
Basically there are two mandatory arguments of the IFS formula in Excel. However, you can check for up to a maximum of 127 conditions in excel using this function.
- logical_test1 – In this argument, enter the first condition that is to be checked by excel.
- value_if_true1 – Enter the text/value to return if the condition specified in the first argument fulfills.
Similarly, if you can provide more such conditions and corresponding return values, up to 127 conditions and values. However, it is optional.
Do Not Miss These Points
- The result of the IFS excel function is either TRUE or FALSE based on the fulfillment of a condition. It means, if the condition fulfills, then the formula would return TRUE, else it would return FALSE. In other cases, it would give an error.
- As mentioned earlier, the IFS function in Excel returns that the value of the condition that fulfills first. For example, if logical_test45 is TRUE, then excel would return value_if_true45 in the cell, and therefore, it would then not check for the subsequent conditions (logical_test46, logical_test47, and so on).
- If all the conditions return FALSE, then excel would return the #N/A error in the excel cell, which means that none of the conditions has fulfilled.
Using Excel IFS Function with Examples
Let us now consider some simple examples to understand how IFS function and formula works in excel.
One of the practical utility of IFS function is when you want to find the sales rep’s commission amount based on sales value.
The below image shows the actual sales made by each of the salesmen in a particular month. Also, the table on the right-hand side shows the commission percentage based on sales achievement.
The below formula would help you to get the commission percentage in cell C2.
=IFS(B2>=$E$6,$F$6,B2>=$E$5,$F$5,B2>=$E$4,$F$4,B2>=$E$3,$F$3,B2>=$E$2,$F$2)
Definitely, you can use the nested IF function to get the same result. However, trust me, the nested IF function is much more complex to understand and create formula than using the IFS formula.
However, it is important to understand that IFS formula is not the only function that can help to achieve this. In case you are older excel version users (like Excel 2013 or below), then you can use the VLOOKUP function with an approximate match to achieve the same output.
Use this formula in the cell C2-
=VLOOKUP(B2,$E$2:$F$6,2,1)
Thank You 🙂