Use the IFS function instead of nested IF to test for multiple conditions
Available in 365 and 2019 versions of Excel, the IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition.
IFS can take the place of nested IF statements and is much easier to construct and read when testing for multiple conditions.
IFS can also be used with other functions, such as OR and AND.
Let’s break down the IFS function formula in the Status column below. Notice that if none of the conditions evaluate to TRUE, we simply add the TRUE condition to the end of the IFS formula as a catch-all to avoid an #N/A error.
Status Column Formula: =IFS(E4="Y","Paid",D4<$B$1,$B$1-D4,TRUE,"Not Yet Due")
1. If the Paid? column contains Y, "Paid" should be returned
2. If the Due Date is less than the Report Date, return the difference between the Report Date and the Due Date
3. If neither condition is true, "Not Yet Due" should be returned
Would you like to learn more about the IFS function, or other Excel functions? Contact us!
© 2022 DJH Training & Application Solutions Inc.