Google Sheets IFS: How It Works and When to Use It

In Google Sheets, IFS checks conditions in the order you list them and returns the result paired with the first condition that is TRUE. Use it to replace a long chain of nested IF functions; if none of its conditions match, add a final TRUE condition to provide a fallback instead of getting an error.

What IFS does in Google Sheets

IFS is a logical function for choosing an output from multiple possibilities. Each condition is tested in sequence, and each result belongs to the condition immediately before it. As soon as a condition is true, Sheets returns its paired result and does not use later pairs to choose another answer.

That makes the function useful for assigning categories, labels, or actions based on a value. For example, you might convert a test score into a letter grade, turn an order status into a next step, or classify a delivery time as early, on time, or late. The function returns one value for each evaluation; it does not automatically explain which condition matched.

IFS is not a replacement for every kind of lookup. It works best when you have a manageable set of conditions that can be written clearly in the formula. If you are matching many category names against a reference table, a lookup formula or a separate mapping table may be easier to update.

Syntax and the order of its arguments

The pattern is IFS(condition1, value_if_true1, [condition2, value_if_true2, ...]). The square brackets in this description mean that later condition-and-result pairs are optional; do not type the brackets into your formula. You need at least one condition and its corresponding result, and every additional condition needs a matching result.

A condition is a test that evaluates to TRUE or FALSE, such as A2>10 or B2="Paid". A result can be text in quotation marks, a number, a cell reference, or another expression. The function requires a condition-result pair for every case you want it to handle.

These examples use commas between arguments. Google Sheets may use semicolons instead, depending on the spreadsheet’s locale. If a formula with commas produces a formula parse error, check the locale under File > Settings and use the separator expected by that sheet.

Example: assign grades from a score

Suppose scores are in B2:B20, with one score per row, and you want to assign grades using 90, 80, 70, and 60 as cutoffs. Enter this formula in C2, then copy it down the column. The conditions are listed from the highest threshold to the lowest so that a score is assigned the best applicable grade.

With the score in B2, the formula is =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F"). If B2 contains 84, the result is B: the first test is false, while the second test is true. If B2 contains 58, all four score tests are false, so the final TRUE condition returns F.

The last pair is a default case. Since TRUE is always true, it catches any value that has not matched an earlier test. Without that pair, a score of 58 would produce #N/A, because none of the conditions matched. You can choose a different fallback, such as “Check score” or an empty string, according to what makes sense for the sheet.

Using multiple criteria

A condition does not have to compare just one cell with one value. You can use functions such as AND and OR to build a test from several requirements. AND is true only when all its arguments are true; OR is true when at least one argument is true. This lets IFS distinguish cases that depend on both a value and a status.

For example, suppose a score is in B2 and a completion status is in C2. You want a high score to count as a pass only when the work is marked Complete; a high score with another status should be reviewed. With those values in B2 and C2, use =IFS(AND(B2>=90,C2="Complete"),"Pass",B2>=90,"Review",TRUE,"Fail"). If B2 is 93 and C2 is Complete, the result is Pass. If B2 is 93 and C2 is Pending, the first test fails and the second succeeds, so the result is Review.

Put the more specific case first when it overlaps a broader one. In this example, the condition B2>=90 includes every score covered by the first condition, so reversing the first two pairs would return Review for a completed score of 93. The same principle applies whenever two conditions could both be true: IFS returns the earlier match.

How to choose conditions for ranges

When you are classifying numbers into ranges, arrange the conditions so their order produces the intended result. Descending threshold tests, as in the grade example, are often easy to read. You can also use explicit upper and lower bounds, especially if the ranges are irregular or need to be non-overlapping.

Before filling the formula down a column, check values at the boundaries. For cutoffs at 60, 70, 80, and 90, test 59, 60, 69, 70, 79, 80, 89, and 90. This catches common mistakes such as using > when the rule should include the threshold with >=, or placing a broad condition before a more specific one.

Write down what should happen for values outside the expected range. A negative score, a value above 100, or text in a score column might not fit the intended rules. A default result can make unexpected cases visible rather than quietly assigning them a normal category. If different invalid inputs need different messages, add explicit checks before the ordinary range tests.

Missing matches, blanks, and text

If every condition evaluates to FALSE, IFS returns #N/A. That is a signal that no case matched, not necessarily evidence that the formula is broken. Add a final condition such as TRUE with a fallback value when you want every possible input to return something. For a data-quality workflow, a message like “Review” can be more helpful than a blank because it makes unclassified rows easy to find.

Blank cells deserve deliberate handling. A blank referenced by a numeric comparison can behave in ways that may not match the meaning you intend, so test for blankness explicitly when missing data should have its own result. For instance, if B2 contains a score and an empty score should display “Missing,” put the blank check first. With the score in B2, use =IFS(B2="","Missing",B2>=60,"Pass",TRUE,"Below threshold"). An empty B2 returns Missing; a value of 72 returns Pass; and a value of 45 returns Below threshold.

For text conditions, quote literal words, as in B2="Paid", but do not put quotation marks around a cell reference. Make sure the input text is consistent: extra spaces or different spellings can stop an exact comparison from matching. If entries are user-entered, consider cleaning or standardizing the source data rather than adding a long collection of slightly different text checks.

Common errors and how to troubleshoot them

A formula parse error usually points to a syntax problem, such as a missing comma, an unclosed quotation mark, or an argument separator that does not match the spreadsheet locale. Check that every condition has a corresponding result and that parentheses are balanced. A result containing text needs straight double quotation marks around it.

A #N/A result usually means that no condition evaluated to TRUE. Review the input and conditions, then decide whether a default result is appropriate. A result that seems wrong but is not an error often comes from the order of conditions: an earlier broad condition may be capturing a value before a later, more specific condition gets considered.

If a formula works in one row but not another, inspect the references as you copy it. References such as B2 adjust to B3, B4, and so on when filled down. If a condition should always refer to one fixed cell, use an absolute reference such as $E$1. Check also that the source values have the type you expect; a number stored as text may not behave like a numeric score in comparisons.

IFS compared with IF and SWITCH

Nested IF functions can express the same kind of branching logic, but long chains can become difficult to read because each IF is placed inside another. IFS lays out condition-result pairs in order, which can make a sequence of thresholds easier to scan. For a simple two-outcome decision, ordinary IF is often shorter and clearer. Choose the form that makes the rule easiest to maintain, rather than using IFS just because it exists.

SWITCH is often a better fit when one expression is being compared with several exact values, such as mapping the text “Small,” “Medium,” or “Large” to another label. IFS is more flexible when the tests are different comparisons, ranges, or compound conditions. Neither approach removes the need to think about missing or unexpected values: include a default result where appropriate and make sure it communicates what should happen.

In current Google Sheets, the core IFS pattern is similar to IFS in modern Microsoft Excel, so a straightforward formula will often transfer between them. However, this page describes Sheets in a web browser, and spreadsheet behavior can depend on locale and application version. Older Excel releases may not support IFS; when sharing a file, check the recipient’s version or use a compatible nested IF formula if needed.

Making an IFS formula easier to maintain

Keep the tests in a logical order and use result labels that describe the outcome clearly. If the conditions are complicated, add a short note in a nearby cell explaining the rule, or split the logic into helper columns. A helper column can show a component of a test separately, making it easier to spot whether an unexpected result comes from the input or the final classification.

For rules that change often or contain many categories, store thresholds or category mappings in a small table instead of embedding every value in one formula. That lets someone update a cutoff in a cell rather than edit the formula itself. It can also make the rules visible to people who do not regularly work with formulas. IFS remains useful for a small, stable set of cases; a reference table is often easier to manage as the list grows.

Frequently Asked Questions

What happens if no IFS condition is true?

Google Sheets returns #N/A when none of the listed conditions is TRUE. Add a final TRUE condition and a result to catch unmatched values, for example TRUE paired with “Other”.

Does IFS stop at the first true condition?

It returns the result paired with the first condition that is TRUE in the order written. Therefore, place more specific or higher-priority conditions before broader conditions that could also match.

Can I use AND or OR inside IFS?

Yes. Use AND when all parts of a condition must be true and OR when any one of them can be true. Each combined test still needs its own result in the IFS argument list.

Why is my Google Sheets IFS formula showing a parse error?

Check for missing condition-result pairs, unmatched parentheses, and quotation marks around text. Also check whether the spreadsheet locale expects semicolons instead of commas between arguments.

Can I use IFS to check whether a cell is blank?

Yes. Include a blank test such as a reference equal to an empty string, and put it before conditions that would otherwise classify the blank input. That makes the intended missing-value behavior explicit.

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.