The IFS function in Excel is similar to the IF function, in that it lets you write statements to return a certain value if a condition is true. But unlike the IF function, which tests a single condition, the IFS function lets you test a long list of conditions (up to 127) and returns the value associated with the first true condition.
The IFS function is a good alternative to using multiple nested IF statements because it's easier to keep the different conditions and return values organized.
The IFS function can take up to 127 pairs of conditions and values.
=IFS(condition_1, value_if_true_1, [condition_2, value_if_true_2, ...])The first condition and value_if_true are both required. Any subsequent pair of condition and value_if_true values are optional.
Conditions and values must be included as pairs. In other words, you can't have one without the other. If your formula contains condition_2 it must also contain value_if_true_2, otherwise Excel will return an error.
The IFS function evaluates each condition in order until it finds a true condition. Then it returns the value_if_true associated with the first true condition it finds.
For example, it first evaluates condition_1. If true, the function returns value_if_true_1. If condition_1 is false, it then evaluates condition_2, and so on.
To use the IFS function or any other logical functions in Excel you'll need a good understanding of Excel's logical operators, which are the foundation of conditions which evaluate to true or false.
Say we are using Excel to track the number of deals closed by each employee, and employees can earn different bonuses depending on the number of deals they close. Your boss asks you to write an Excel formula that will calculate the bonus for each employee.
Your boss tells you that any employee who closes less than 3 deals doesn't get a bonus at all. Anyone who closes 3 or more deals gets $200. Any employee who closes 4 or more deals gets $500. Finally, any employee who closes 5 or more deals earns a bonus of $1,000. An employee can earn one bonus, but not multiple.
Double-click into cell D2 (column D, row 2) in the example Excel spreadsheet above and type the following formula:
=IFS(C2>=5, 1000, C2>=4, 500, C2>=3, 200, C2<3, 0)The formula returns a value of 500. Why? Excel first evaluated whether the value in cell C2 (which is 4) was greater than or equal to 5. The condition is false, so it then evaluates the next condition: is the value in cell C2 greater than or equal to 4? This time the condition is true, so the formula returns the value 500.
So why did we write the condition and value pairs from largest to smallest? Recall that the IFS function returns the value of the first true condition it encounters. If we wrote the conditions in the opposite order, we would have calculated a bonus of 200 for the first employee because the expression C2>=3 is true, so the formula would return the associated value and would not check the rest of the criteria.
Let's copy the formula to calculate the bonus for the second employee. In cell D3 of the example spreadsheet above, modify the formula to the following:
=IFS(C3>=5, 1000, C3>=4, 500, C3>=3, 200, C3<3, 0)The formula now returns a value of 0. The IFS function evaluates the first criteria, C3>=5, which is false. It then evaluates the second criteria, C3>=4, which is also false. It then evaluates the third criteria, C3>=3, which is still false. Finally, it examines the final criteria, C3<3, which is true because the value in cell C3 is less than 3. Because the final criteria is true, the function returns the final value, which is 0.
Let's skip down to the last employee in the list and try the formula on this example.
=IFS(C6>=5, 1000, C6>=4, 500, C6>=3, 200, C6<3, 0)In this case your formula will return 1000. IFS will evaluate the first criteria, C6>=5, conclude that it is true, and return the first value_if_true, which is 1000.
Like any function in Excel, IFS might return a number of errors.
The IFS function returns the #VALUE! error when a condition argument does not evaluate to true or false. Double-check each condition in the formula and ensure that it is a logical statement that evaluates to true or false. If necessary you can check out the logical operators in Excel.
The IFS function will return the #N/A error when none of the logical conditions are true. For example, say we have the following formula:
=IFS(A1>5, "Big", A1>2, "Small")This formula would return the #N/A error if the value in cell A1 is less than or equal to 2, because none of the logical conditions in the formula is true.
To avoid this error, you can specify a default value for the IFS function as described below.
If you want the IFS function to return a default value if no conditions are true, you can simply write TRUE for the final condition, followed by a final value_if_true. Take the example we used earlier:
=IFS(A1>5, "Big", A1>2, "Small")This formula will return the #N/A error for any value less than or equal to 2 because no conditions would be true.
Let's add a default return value:
=IFS(A1>5, "Big", A1>2, "Small", TRUE, "Unknown")This formula evaluates each condition and, if the first two conditions are false, will get to the final condition. The final condition is TRUE, so the formula will return "Unknown" by default if no other conditions are true.