IFS Syntax in Google Sheets: Examples and Common Errors

In Google Sheets, IFS checks conditions in order and returns the value paired with the first condition that is TRUE. Its syntax is =IFS(condition1, value1, [condition2, value2, ...]); add a final TRUE condition with a fallback value when you want a result for every case.

Google Sheets IFS syntax

IFS takes alternating condition-and-result arguments: first a logical test, then the value to return if that test is true, followed by another test and its result if needed. The brackets in the documented pattern indicate optional additional pairs; they are not characters to type into a formula. In a working formula, begin with an equals sign and separate arguments with commas, as in the examples below.

A condition is an expression that evaluates to TRUE or FALSE. It can compare a cell with a number, text, a date, or another cell; it can also use a logical function such as AND or OR. A result can be text in double quotation marks, a number, a cell reference, or another expression. Text returned by a formula needs quotation marks, while numbers do not.

For example, a comparison such as B2>=90 is a condition, and the text result “A” is a value. IFS pairs them in that order. Keep each condition immediately beside the result it controls so the formula remains easy to read and revise.

How IFS evaluates conditions

IFS proceeds through its condition-result pairs from left to right and returns the result for the first condition that evaluates to TRUE. Later conditions do not determine the answer once a match has been found. This makes order important when conditions overlap. If a value meets both a broad rule and a more specific rule, put the specific rule first.

Suppose a discount table gives a higher discount to purchases of at least $500 and a lower discount to purchases of at least $100. If the lower threshold is tested first, a purchase of $600 also meets that lower threshold, so the higher discount may never be reached. Test the $500 threshold first, then the $100 threshold, and provide a final fallback if smaller purchases need a result.

Every condition-result pair must be complete. A condition without its paired result, or an extra result without a condition, makes the formula invalid. For clarity, count arguments in pairs as you build the formula: one test, one outcome, then the next test and outcome.

Practical IFS examples

Assign letter grades. Assume a student’s numeric score is in B2, on a scale from 0 to 100. The formula tests the largest thresholds first, so each score receives the highest grade whose cutoff it meets.

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

A score of 84 returns B; a score of 59 returns F. The final TRUE acts as the catch-all condition: because a condition is always true, it supplies a result if none of the score thresholds matched. If scores might be negative or greater than 100, decide separately whether those values should be rejected or handled as grades.

Label order sizes. Assume the quantity for each order is in A2. This formula labels quantities of 100 or more as Large, 50 through 99 as Medium, and anything below 50 as Small.

=IFS(A2>=100,"Large",A2>=50,"Medium",TRUE,"Small")

With A2 equal to 72, the result is Medium. The order of the tests matters: placing the 50 threshold first would classify 100 as Medium, since 100 is also at least 50.

Return a blank for an empty input. Assume A2 contains a quantity, but some rows are not yet filled in. Put the blank check first so an empty input is not treated as a small quantity.

=IFS(A2="","",A2>=100,"Large",A2>=50,"Medium",TRUE,"Small")

When A2 is empty, the result is an empty string, which appears blank in the cell. This is different from a cell that contains a space or the number zero; if those values are possible, consider how they should be categorized rather than assuming they are empty.

Choosing a default result

IFS does not include a separate ELSE argument. If all listed conditions are false and there is no matching condition, Google Sheets returns the #N/A error. This behavior can help reveal missing cases, but it can be surprising when a formula is intended to classify every row. Adding TRUE and a final result makes the fallback explicit.

Choose a fallback that communicates what the data means. For a grade formula, “F” may be suitable only if every valid score below the last cutoff is an F. In a review workflow, “Check input” may be more useful than silently assigning an ordinary category. If an input can be blank, handle that condition before the general fallback, as in the blank-input example.

A default does not validate the input. For example, TRUE will also catch an unexpected word in a cell if earlier tests are numeric comparisons. If the formula must distinguish invalid data from legitimate low values, add an explicit validation condition or use a separate data-validation rule. Think through likely inputs—including blanks, text, zero, and out-of-range numbers—before choosing the final result.

Using AND and OR inside IFS

Each IFS condition can combine tests with AND or OR. AND is useful when a result requires multiple facts to be true; OR is useful when any one of several alternatives should qualify. The condition still returns a single TRUE or FALSE for the row, and its paired result follows it as usual.

For instance, assume a student’s score is in B2 and attendance percentage is in C2. This formula labels a student as Eligible only when the score is at least 70 and attendance is at least 80 percent; otherwise, it returns Review.

=IFS(AND(B2>=70,C2>=80%),"Eligible",TRUE,"Review")

The AND condition requires both tests to pass. If either is false, the final TRUE condition supplies Review. To qualify someone when either of two conditions is met, use OR in the condition instead. Be careful with percentage inputs: if C2 is formatted as a percentage, a value displayed as 80% is stored as 0.8, and the comparison to 80% uses the same scale.

Common errors and troubleshooting

#N/A when no condition matches: Add a final TRUE-and-result pair if you want a fallback, or add a condition that covers the missing case. Without one, no match can produce #N/A.
Wrong category despite a valid formula: Check for overlapping thresholds and reorder the conditions from most specific or highest threshold to least specific. IFS uses the first true condition, not the best-fitting condition later in the list.
Formula parse error: Check for a missing comma, closing parenthesis, quote, or condition-result value. In some spreadsheet locales, formulas use semicolons rather than commas as argument separators. The examples here use commas; if your sheet expects semicolons, use the separator required by that locale.
Text is not matching: Text tests need quotation marks around literal text, and the cell content must match the condition. Extra spaces or inconsistent spelling can prevent an expected match. A condition comparing a cell to the text “Yes” is not the same as a test for a numeric value.
Blank rows receive an unexpected label: Add a blank-cell condition before broad rules, or decide what blank should mean. A final TRUE fallback catches blanks too unless an earlier condition handles them.
Results change after adding a rule: Review the new condition’s position. A new broad condition placed near the beginning can capture values that were previously handled by a later, more specific rule.

When a formula becomes hard to audit, temporarily test its component conditions in separate cells or use a small sample table with known inputs and expected outcomes. This helps separate a logic-order issue from a typing or data-quality issue.

IFS compared with IF and SWITCH

IFS is a useful replacement for a long chain of nested IF functions when you have several different conditions and corresponding outcomes. Each rule appears as a pair in one function, which can make a grading scale or set of thresholds easier to scan. Nested IF remains useful when a decision has only one or two branches, or when a result needs a more involved structure that is clearer with explicit nesting.

For comparison, assume the score is in B2. This nested IF formula uses the same grade thresholds and fallback as the earlier IFS example.

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

SWITCH is often a better fit when one expression is being compared with a list of exact values, such as a code that can be “New,” “Open,” or “Closed.” IFS is generally more natural for ranges and different logical tests, such as a score greater than or equal to a cutoff. Both approaches depend on writing rules that cover the intended cases.

Google Sheets and modern Excel both support IFS, but the examples and descriptions here are for Google Sheets in a web browser. Excel’s formula language is similar for these examples, though spreadsheet locale can affect separators in either program. Google Sheets formulas are entered directly into cells; Excel ribbon and menu instructions are not needed to use this function.

Building and checking an IFS formula

Before entering a longer formula, write down the rules in plain language and decide what should happen when no rule matches. Then translate each rule into a condition-result pair, ordering overlapping conditions deliberately. This small planning step reduces errors more effectively than adding conditions until the formula appears to work.

Identify the input cell or cells for the current row.
List each condition and its intended result, including blank and unusual inputs where relevant.
Order the conditions so an earlier rule does not accidentally capture a case meant for a later rule.
Enter the formula in the result column and test one example from every category, plus boundary values at each cutoff.
Copy the formula down the column if appropriate, then inspect a few lower rows to confirm cell references adjust as intended.

Boundary checks are especially valuable. For thresholds of 90, 80, and 70, test 90, 89, 80, 79, 70, and 69—not only round numbers far from the cutoffs. If formulas are copied down, relative references such as B2 normally shift to B3, B4, and so on. If a condition should always refer to a fixed cell, use an absolute reference such as $B$2.

Frequently Asked Questions

What is the IFS formula in Google Sheets?
IFS checks logical conditions in order and returns the result associated with the first TRUE condition. Use alternating condition-and-result arguments, and add a final TRUE condition with a fallback if every possible input needs a result.

Does Google Sheets IFS need a default value?
No, but it is often useful. If no condition is true and there is no catch-all condition, IFS returns #N/A. A final TRUE condition supplies a default result for cases not covered earlier.

Why does my IFS formula return #N/A?
Usually, none of the conditions matched. Check the input values and comparison boundaries, then decide whether to add a condition for the missing case or a final TRUE fallback. The error can also be intentional if unmatched inputs should be flagged.

Can I use AND or OR in Google Sheets IFS?
Yes. Use AND when all requirements in a condition must be true, or OR when at least one alternative should be true. Put the combined logical test in a condition position and pair it with the result to return when it passes.

What is the difference between IFS and IF?
IF handles one logical test and its true and false outcomes. IFS handles several condition-result pairs in sequence, making it convenient for multiple categories or thresholds. Use whichever makes the decision easiest to understand and maintain.

Does IFS work in Excel too?
IFS is also available in modern versions of Excel, including Microsoft 365 and Excel 2019 or later. The core condition-and-result logic is similar, though argument separators may vary with the spreadsheet’s locale.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.