Prior to Microsoft Excel 2016, it was quite a time-consuming task if you needed to evaluate multiple conditions that ended up with multiple results. You would solve this the old way with multiple nested IF functions as show in the example below:
=IF(D3>=1500,”Accepted”,IF(C3>=3,”Conditionally Accepted”,IF(E3=100%,”Accepted if Space Available”,”Rejected”)))
The IFS function gives you an efficient alternative to the long and tedious task of creating a nested IF function. The IFS function checks conditions sequentially, and as soon as a condition is found to be true, the function stops and returns the specified value.
So, accomplishing the same goal as the multiple nested IF functions can be done with a single IFS function like this:
=IFS(D16>=1500,”Accepted”,C16>=3,”Conditionally Accepted”,E16=100%,”Accepted if Space Available”,1=1,”Rejected”)
The Microsoft Excel IFS function evaluates multiple conditions starting from left to right and returns the corresponding value of the first condition that is true. If you need a default value to be displayed if none of the conditions are true, you can a condition at the end that is always true, like 1 = 1 and assign it the default value. Say goodbye to nested IF functions and instead use the IFS function to evaluate multiple conditions with multiple results. The IFS functions are shorter and easier to read.
=IFS([Some Condition is True1, Value if True1, [Some Condition is True2, Value if True2],…[Some Condition is True127, Value if True127])
- Some Condition is True1 – First Logical Test
- Value if True1 – Result When Condition 1 is true
- [Some Condition is True2] – [optional] Second Logical Test
- [Value if True2] – [optional] Result When Condition 2 is true
- [Some Condition is True…]
- [Value if True…]
Function Return Value
The value of the first condition that is true.
Supported in Microsoft Excel Versions
Microsoft Excel 2016 and later. This includes both Office 365 Microsoft Excel 2016 and stand alone Microsoft Excel 2016 versions.