Instead of using IF with OR, use IFS when you want to check multiple conditions one by one in Excel.
What’s the difference between IFS and IF functions? What’s the IFS formula in MS Excel?
After reading this quick Excel tutorial, you’ll know!
Ready to learn?
IFS vs IF: what’s the difference?
The IF function allows you to compare a value with an expected value while IFS in Excel checks whether one or more conditions are met and will return a value that corresponds to the first TRUE condition. If several conditions are met, only the value from the first TRUE condition will be returned.
As a reminder, the IF formula is =IF(logical_test, value_if_true, [value_if_false]) and can be used with AND or/and OR statements.
Click here to learn more about IF with AND/OR.
What’s the IFS formula in Excel?
The IFS syntax is =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…).
IFS includes 2 required arguments in Microsoft Excel: logical_test1 and value_if_true1.
logical_test1 — a condition that can be TRUE of FALSE.
value_if_true1 — the result of IFS if logical_test1 is TRUE.
Notes
– You can test up to 127 different conditions in Excel using IFS.
– As soon as one condition is met, the system will not check for other conditions and will return the value_if_true of the first TRUE logical_test.
– If no TRUE conditions are found, you’ll see an N/A error. You can change this error message by combining IFS with IFNA. (Learn about IFNA here.)
Use of IFS in Excel — Example with 4 conditions
In this example, instead of using IF with multiple arguments thanks to OR, we use IFS (like pros do!).
We want to check if some of our students did not start their Online Course yet: if so, we want to contact them. We also want to track the completion level of other students.
The formula we use is =IFS(C42=0,”Contact them”,C42=100,”Certified”,0<C42,”In progress”,C42<100,”In progress”).
We also used Conditional Formatting to highlight cells containing “Contact them”. Learn how to do it here.
Use of IFS in Excel — Example with IFNA
Combine IFNA and IFS in case no conditions are met so no N/A error will appear.
In this example, we just want to find students who got they MS Excel certificate or who did not start the Course yet so we can contact them. We don’t want anything to appear when students are still learning.
The formula we use is =IFNA(IFS(C42=0,”Did not start yet”,C42=100,”Certified”),””).
For your information, without IFNA, the formula would be =IFS(C42=0,”Did not start yet”,C42=100,”Certified”) and you can see how it would look in the last column.
Now, you know how to use IFS instead of IF or IF or IF in Excel.
Want to learn more Excel tips and tricks? Join our Certifying Excel Course now!
