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.
Learn more about IF with AND/OR, click here.
What is the IFS function in Excel?
The IFS formula 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.
Tips about IFS in Excel
– 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.)
IFS function in Excel — Example with 4 conditions
In this example, instead of using IF with multiple arguments thanks to OR, we use the IFS function (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.
IFS function 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 the IFS function in Excel and the difference between IF vs IFS.
Want to learn more Excel tips and tricks? Join our Certifying Excel Course now!
