IFS
Check multiple conditions at once and return the value for the first one that's true.
Logical
| Excel 2019+
| Google Sheets Supported
Spreadsheet editor
Spreadsheet editor
Syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2, ...], [default_value])
Returns: Varies Arguments
| Argument | Required | Description |
|---|---|---|
| logical_test1 | Yes | logical_test2, ..., The logical tests or conditions to evaluate. |
| value_if_true1 | Yes | value_if_true2, ..., The values to return if the corresponding logical test is true. |
| default_value | Yes | (Optional) The value to return if none of the logical tests are true. If omitted and no logical test is true, Excel will return a #N/A error. |
About
IFS evaluates a list of conditions from left to right and returns the value paired with the first condition that's TRUE. It's the cleaner alternative to deeply nested IF statements when you have three or more rules to check.
Use IFS any time you need to assign a label, tier, or result based on a ranked set of rules. Grading scales, sales commission bands, performance categories, and inventory status checks are all good fits. Order your conditions from most specific to least specific, since IFS stops at the first match.
To cover leftover cases, add TRUE as your final condition with a default value. Without it, IFS returns #N/A when nothing matches. If you need to handle errors in the result, wrap the formula with IFERROR.
Use IFS any time you need to assign a label, tier, or result based on a ranked set of rules. Grading scales, sales commission bands, performance categories, and inventory status checks are all good fits. Order your conditions from most specific to least specific, since IFS stops at the first match.
To cover leftover cases, add TRUE as your final condition with a default value. Without it, IFS returns #N/A when nothing matches. If you need to handle errors in the result, wrap the formula with IFERROR.
Exercises using IFS
Examples
What happens without a TRUE fallback
See what happens when IFS has no catch-all condition. T-102 has a score of 3. It falls through every test, so the 'No fallback' column returns #N/A. Change that score and watch column C update. The 'With TRUE fallback' column always returns a result.
Spreadsheet editor
Why condition order matters
Notice that Emma scores 92 but the 'Wrong order' column gives her a D. Because IFS stops at the first match, the broad >=60 condition fires before >=90 ever gets checked. The 'Correct order' column puts the highest threshold first. Try editing a score to see the difference.
Spreadsheet editor
Combining IFS with AND for two-variable decisions
Use IFS with AND when a decision depends on two factors at once. Each condition here checks both weight and distance together. Try changing the weight or distance for any order and watch the shipping tier update automatically.
Spreadsheet editor
Watch out for
⚠
No fallback for unmatched conditions
IFS returns #N/A when none of its conditions are TRUE. This can happen when an unexpected edge case slips through.
→ Add TRUE as the final condition with a default value, such as =IFS(..., TRUE, "Other"). This guarantees IFS always returns something.
⚠
Conditions in the wrong order
IFS stops at the first TRUE condition. If you put a broad condition like B2>=60 before a narrower one like B2>=90, high scores will match the wrong bucket first.
→ Always order conditions from highest to lowest (or most specific to least specific) so narrower rules get checked first.
⚠
Conditions that don't return TRUE or FALSE
If a condition evaluates to something other than TRUE or FALSE (for example, a number or text string), IFS returns a #VALUE! error.
→ Use comparison operators (>, <, =, >=, <=, <>) in the condition to produce a TRUE or FALSE result.
Tips & notes
IFS supports up to 127 condition/value pairs. For large sets of rules, a lookup table combined with XLOOKUP is often easier to maintain than a long IFS formula.
Common questions
What is the difference between IFS and nested IF?
IFS lays all conditions out side by side in one formula, which makes it much easier to read and update. Nested IF embeds each condition inside the previous one, which works but gets hard to follow as the number of conditions grows.
What does IFS return if none of the conditions match?
It returns a #N/A error. To avoid this, add TRUE as the last condition in your formula with a default value to return, like =IFS(A1>=90,"A",...,TRUE,"F").
Can IFS return numbers, text, and formulas as values?
Yes. Each value_if_true argument can be a number, text string, formula, or cell reference. The data types don't need to match across conditions.
When should I use SWITCH instead of IFS?
SWITCH is a better choice when you're comparing one value against a list of exact matches, like checking if a code equals 1, 2, or 3. IFS is better for range-based comparisons like greater than or less than.