How to Use the IFS Function in Excel

The Excel IFS function, which first appeared in 2019, allows you to test multiple conditions (known as logical tests) in a single entry. It returns a corresponding value for the first condition that evaluates as true. If you’re familiar with this concept, you’ve probably done it by nesting multiple IF statements. However, using the IFS function not only saves time but also enhances the accuracy and readability of your spreadsheets. 

Need help understanding other conditional functions? Check out our handy summary of all Excel IF functions here.

Syntax

The syntax of the IFS function is:

=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, logical_test3, value_if_true3…)

Logical_test1 is the condition (i.e. formula or expression) to be evaluated or tested, for example, A1=100 is testing to see if the number in A1 is equal to 100

Value_if_true1 is the value to return if logical_test1 is true. This can be text, a number, a date, or a formula. If the value is text, it has to be enclosed in double quotes, for example “Bonus to be paid”.

Logical_test2 and any subsequent logical_tests (the IFS function can include up to 127 logical tests) are optional conditions to be evaluated. Each logical test has a corresponding value_if_true.

If none of the logical tests are true, Excel returns #N/A. To avoid this you can optionally add two arguments at the end of the function. The first (i.e. penultimate) argument will always be the word TRUE (or true - it is not case sensitive), and the final argument will be whatever value you want to be placed in the cell in the event that none of the logical tests are true.

=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, logical_test3, value_if_true3, TRUE, value_if_no_test_is_true)

Note that logical tests are applied in the order in which they appear in the formula. If a logical test is found to be true, Excel returns the associated value without evaluating the remaining tests.

Worked example

The screenshot below is from a spreadsheet that relates to feedback from people who attended a training course. 

At the conclusion of the course, the attendees are asked to rate the course on a scale of 1-5. For reporting purposes, these scores have to be translated into text-based descriptions:

5 = Excellent; 4 = Very Good; 3 = Good; 2 = Poor; 1 = Very Poor

This can be achieved using an IFS function as follows:

=IFS(B2=5,"Excellent",B2=4,"Very Good",B2=3,"Good",B2=2,"Poor",B2=1,"Very Poor")

B2=5 is a logical test and “Excellent” is its associated value.

B2=4 is a logical test and “Very Good” is its associated value, and so on. 

The function can be translated as “If the value in cell B2 is the number 5 then put the word Excellent into the cell (where the function is). If the value in cell B2 is the number 4 then put the words Very Good into the cell (where the function is)," and so on.

If the first logical test is true (i.e. B2 does contain the number 5) Excel places the corresponding value into the cell as the function’s result. Once the result has been determined Excel does not apply the other logical tests.

However if the first logical test is not true (to use Excel’s language it evaluates to false), so in this example suppose B2 does not contain the number 5, Excel checks the next logical test, i.e. does B2 contain the number 4? If that is true Excel places that logical test’s corresponding value (Very Good) into the cell as the function’s result. 

What happens if none of the logical tests are true? In the screenshot below, the IFS function in cell C2 returns #N/A because B2 contains a value (6) that has not been accounted for within the logic of the tests. 

To some people #N/A is no big deal. We know what it means. We don’t panic or think we’ve broken the spreadsheet. However, best practice dictates that you “include a way to deal with this situation gracefully”. As explained in the Syntax section above, to do this, you add 2 additional arguments at the end of the function:

1. The word TRUE (or true - it is not case sensitive) without double quotes.

2. The value to be placed in the cell.

=IFS(B2=5,"Excellent",B2=4,"Very Good",B2=3,"Good",B2=2,"Poor",B2=1,"Very Poor",TRUE,”Invalid Score”)

Using the above, if B2 does not contain 1,2,3,4 or 5, the words “Invalid Score” will be placed into the cell containing the function.

Comparison operators

The logical test element of IFS is not limited to “Are these items equal?” (i.e. is the value in B2 equal to 5?). These are the other comparison operators that can be used:

The example below relates to 5 students and their scores in a recent exam. 

The teacher has used the following in C2 (which is then copied to C3:C6) to generate the appropriate grade:

=IFS(B2 >= 90, "A", B2 >= 80, "B", B2 >= 70, "C", B2 >= 60, "D", B2 < 60, "E")

This can be interpreted as follows…

If the value in B2 is 90 or higher, the grade is A.

If the value in B2 is 80 or higher (but less than 90), the grade is B.

If the value in B2 is 70 or higher (but less than 80), the grade is C.

If the value in B2 is 60 or higher (but less than 70), the grade is D.

If the value in B2 is less than 60, the grade is E.

This looks a little confusing because 85 (in B2) is greater than or equal to 80 but it is also greater than or equal to 70 and it is also greater than or equal to 60. So how does Excel know whether to assign B,C, or D? 

Remember, the logical tests are performed in the order that they are listed and once the result has been determined Excel does not apply the other logical tests. So first Excel checks to see if the value in B2 is 90 or more. No it is not. So then it checks to see if it is 80 or more, which it is, so at this point we have a result (B) and the other logical tests aren’t applied.

Alternative to IFS: Nested IF functions

The great thing about Excel is that there are often multiple ways to approach the same problem. Returning to the course feedback example, instead of using the IFS function we could have used:

=IF(B2=5,"Excellent",IF(B2=4,"Very Good",IF(B2=3,"Good",IF(B2=2,"Poor",IF(B2=1,"Very Poor","Invalid Score")))))

This type of structure, functions inside functions, is known as nesting. In this example, there are five IF functions “nested” within the one cell, and although in principle there is nothing wrong with this, it is long-winded, cumbersome to edit, cumbersome to troubleshoot, and cumbersome to understand. Imagine that you are the new Training Manager and your first task is to understand the logic behind the “score-to-description conversion calculation" – this would be an unpleasant exercise with the above formula.

Then there is the issue of the brackets/parentheses and where they should go. Someone trying to understand the logic of an existing IF function or a less experienced user who is writing their first nested IF function would need to understand “the brackets rule” which is that the closing brackets are placed at the end of the entire function and there has to be one closing bracket for each opening bracket. As an aside, I have been known to run out of fingers when counting the number of opening brackets with particularly complex nested IF functions!

Finally, with the nested IF scenario, a less experienced user may be confused by the fact that the final argument (Invalid Score) does not have a corresponding logical test.

Alternative to IFS: The SWITCH function

Another alternative to IFS is the lesser-known SWITCH function. Again, using the course feedback example:

=SWITCH(B2,5,"Excellent",4,"Very Good",3,"Good",2,"Poor",1,"Very Poor","Invalid Score")

In this example, cell B2 is compared to items in a list (5,4,3,2,1). Each item has a corresponding result. (Excellent, Very Good, Good, Poor, Very Poor). The first matching item in the list will then return a result.

The benefit of SWITCH over IFS is that with SWITCH, B2 is referenced only once, whereas with IFS, it is referenced in each logical test.

However, the SWITCH function has disadvantages compared to the IFS function. SWITCH is limited to comparing only one expression and lacks the flexibility to handle multiple logical tests that the IFS function offers. Also, SWITCH cannot handle complex logical conditions, as it is confined to exact matches of a single value, whereas IFS can evaluate a variety of conditions and expressions, making it more versatile for complex decision-making scenarios.

Wrap up

Mastering the IFS function in Excel can greatly improve your data analysis skills by simplifying complex logical operations into a single, efficient function. Compared to nested IF functions, IFS is more readable, easier to manage, and reduces the risk of errors associated with multiple parentheses and logical tests.
If you're aiming to enhance your Excel skills, GoSkills provides a comprehensive selection of online Excel courses led by industry experts. Whether you're a beginner or looking to master advanced features, there's a course for you. Explore our offerings now.