IFS Formula in Google Sheets: How to Use It

Use the IFS function in Google Sheets to test conditions in order and return the value for the first condition that is true. Start with =IFS(condition1, value1, condition2, value2), replacing each condition and value with your own. Before you begin, decide what rules to check and what result each rule should return; the order matters because Sheets stops at the first match.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
A column or cell containing the values you want to test, such as scores, quantities, or dates.
A clear set of conditions and corresponding results. For example, decide which score ranges map to which letter grades.

These steps use Google Sheets, not Excel. The IFS function is also available in modern Excel, but menu locations and some behavior around regional separators may differ. In Google Sheets, formulas usually use commas between arguments. If your spreadsheet’s locale expects semicolons, use semicolons in place of commas in the formulas below.

Step-by-Step

Set up the data you want to evaluate. Put the input values in one column and leave a nearby column for the results. For example, enter each student’s score in C2:C and plan to put a letter grade in D2. Keep the input values consistent: scores should be numbers rather than text that merely looks like a number. Add a heading to each column so it is clear what the formula does.
Choose the output cell. Click the first row where you want a result, such as D2 when the first score is in C2. Each formula should refer to the input on its own row. Starting on the same row as the first data item makes it easier to copy the formula down without creating an off-by-one error.
Enter an IFS formula with paired conditions and results. Every condition must be followed by the value to return if that condition is true. With scores in C2 and a grade to appear in D2, enter =IFS(C2>=90,"A",C2>=80,"B",C2>=70,"C",C2>=60,"D",TRUE,"F"). If C2 contains 84, the result is B: the first condition is false, while C2 is at least 80, so Sheets returns the value paired with that condition. The final TRUE condition provides a result for any score that did not meet an earlier threshold.
Check the order of your conditions. IFS checks from left to right and returns the result for the first true condition. In the grade example, the 90-point threshold comes before the 80-point threshold. If you put C2>=60 first, a score of 95 would satisfy that condition and return D before Sheets ever checked the higher thresholds. For descending cutoffs, list the largest cutoff first; for ascending ranges, arrange them so a value cannot match an unintended earlier rule.
Add a sensible fallback result. If none of the listed conditions is true, IFS returns an #N/A error. A final pair such as TRUE,”F” or TRUE,”Check value” catches anything not handled above. TRUE is always true, so it should normally be the last condition. Choose a fallback that makes sense for the data: a grade may reasonably default to F, while a product category or approval status may be safer with a message asking someone to review the value.
Try a second practical example. Suppose stock counts are in B2 and the status belongs in C2. Enter =IFS(B2=0,"Out of stock",B2<=5,"Reorder",TRUE,"In stock"). With B2 equal to 4, the result is Reorder. A count of 0 returns Out of stock because that condition is checked first; a count above 5 reaches the final fallback and returns In stock. Confirm that the rules match the way your organization defines each status.
Use AND when one result depends on a range. IFS accepts logical tests, including AND. With a score in C2 and a result in D2, enter =IFS(AND(C2>=0,C2<60),"Needs support",AND(C2>=60,C2<80),"On track",C2>=80,"Strong",TRUE,"Check score"). If C2 is 72, the result is On track. Each AND expression is true only when both of its limits are satisfied, which helps define non-overlapping ranges. The final condition flags negative, blank, or otherwise unexpected values for review rather than assigning them a normal category.
Confirm the result with known test values. Check at least one input for every rule and one value that should reach the fallback. For the grade example, test 95, 84, 72, 60, and a value below 60. Also test exact boundaries such as 90 and 80, because those are where a greater-than sign versus a greater-than-or-equal sign changes the result. Correct the conditions if any test returns a different outcome from the rules you intended.
Copy the formula to the remaining rows. After confirming D2, select it and use the small square at the lower-right corner of the cell to drag the fill handle down. You can also copy the cell, select the destination range, and paste. A reference such as C2 normally changes to C3, C4, and so on as the formula fills down. Review a few copied rows to ensure each one points to the matching input row.
Make the result column easy to use. Add a heading such as Grade or Stock status, and consider formatting the outputs consistently. If the sheet will be shared, explain the thresholds in a nearby note or a separate rules table. This gives other editors a way to understand why a value received a particular label and makes future changes less likely to introduce a conflicting condition.

Common Mistakes

Leaving out a matching result. IFS needs a condition-value pair for every rule. If you add a condition but forget its result, the formula has an uneven number of arguments and Sheets reports an error. Count the arguments in pairs: condition, result, condition, result. A final TRUE and its result count as one complete pair.

Putting broad conditions first. A broad test can capture inputs meant for a more specific rule. For instance, a lower threshold written before higher thresholds can make all larger values receive the same output. Review conditions from left to right and ask whether an earlier condition would also be true for a value intended for a later rule.

Forgetting what happens when all tests are false. Without a fallback, a value that matches no condition produces #N/A. This often happens when rules cover only some values, or when the data includes blanks and unexpected entries. Add a final TRUE pair when you want a controlled default, or leave it out only when an #N/A result is a useful signal that the rules need attention.

Using the wrong comparison operator. A rule using > 80 excludes exactly 80, while >= 80 includes it. Similarly, < 60 does not include 60. Write down whether each boundary belongs in the lower or upper category, then test the exact boundary values in the sheet.

Mixing numbers and text. A cell that contains the text “84” may not behave like the numeric value 84 in every comparison. Imported data can also include spaces or symbols. Check the input cell’s contents and clean or convert inconsistent data before relying on category results.

Using the wrong argument separator. In most Google Sheets locales, commas separate arguments. Some locales require semicolons instead. If a correctly structured formula produces a parse error, check the spreadsheet locale under File > Settings and use the separator expected by that locale. Do not change commas inside quoted text.

Tips

Keep the formula readable by putting conditions in a deliberate order and using short, meaningful output labels. For complicated rules, draft a small table of input ranges and outputs before writing the function. That makes gaps and overlaps easier to spot than trying to reason through a long formula after it has been entered.

Use a fallback that exposes questionable data when silent classification could cause trouble. For example, “Review score” tells you a value did not fit the expected rules, while a normal category could hide the issue. If blanks should stay blank, make that an explicit first test: for a score in C2 and a result in D2, use =IFS(C2="","",C2>=90,"A",C2>=80,"B",C2>=70,"C",C2>=60,"D",TRUE,"F"). When C2 is empty, this returns an empty string; otherwise it applies the grade rules. This blank check is useful only if a blank input should genuinely produce no visible result.

When copying a formula down, relative references such as C2 adjust by row. If a rule also refers to a fixed cell, such as a threshold stored in F1, use dollar signs to keep that reference fixed when filling. For example, $F$1 remains the same cell as the formula is copied. Test the copied formulas rather than assuming every reference shifted or stayed fixed as intended.

For a small set of straightforward rules, IFS is often clearer than nesting several IF functions. However, it is not a replacement for every kind of lookup. If your rules are numerous or change frequently, keeping thresholds and labels in a separate table may be easier to maintain than editing a long formula. Make a copy of important data before making large-scale changes, especially when filling a formula across many rows.

Troubleshooting

The cell shows #N/A. Check whether any condition matches the current input. If no condition is true, add a final TRUE-value pair or adjust the ranges to cover the valid inputs. If #N/A is intentionally identifying values outside your rules, investigate the source data instead of hiding the error automatically.

The formula shows a parse error. Confirm that the function name is IFS, that each condition has a result, and that parentheses and quotation marks are balanced. Use straight double quotes around text results. Check whether your locale needs semicolons rather than commas, and make sure text containing commas is enclosed in quotation marks.

A result appears wrong even though the formula is accepted. Evaluate the conditions from left to right using the input value. Look for an earlier condition that is already true, a missing equals sign at a boundary, or data stored as text. Temporarily test a single input value against your written rules and compare the result with the intended category.

Blank rows show a category. Empty cells can be treated like zero in some comparisons, so a blank score may fall into the lowest range. Add an explicit blank test at the beginning if blank should produce no output or a message. Also check whether a formula or imported data has placed spaces in cells that appear empty.

The copied formula refers to the wrong row. Click a result cell farther down and inspect its references. If they point to the wrong input row, refill from the correctly aligned starting cell. If a threshold reference moved when it should not, make it absolute with dollar signs before copying.

Frequently Asked Questions

What does IFS do in Google Sheets?
IFS checks multiple conditions in order and returns the result paired with the first condition that evaluates to true. It is useful for assigning labels, bands, or statuses without nesting multiple IF functions.

What is the basic IFS syntax?
The pattern is =IFS(condition1, value1, condition2, value2), with additional condition-value pairs as needed. Each condition is a logical test, and each value is what Sheets returns when that test is the first true one.

Does IFS need a final TRUE condition?
No, but without a matching condition the function returns #N/A. Add a final TRUE and a suitable result when you want a default, such as a review message or a category for remaining valid values.

Can I use IFS with text or dates?
Yes. Conditions can compare text or dates as well as numbers, as long as the inputs are stored in a consistent format. For dates, use real date values rather than text that only looks like a date, and test the boundary dates in your rules.

Can I combine conditions in IFS?
Yes. Use AND when all parts of a rule must be true, or OR when any one of several tests should qualify. For example, a range often needs AND to check both its lower and upper limits. Keep the conditions ordered so that the first qualifying rule is the one you want returned.

Is IFS the same in Google Sheets and Excel?
The core idea and familiar condition-result pattern are similar in current versions of both programs. This guide uses Google Sheets in a browser; menus, locale settings, and formula separators can differ in Excel or between spreadsheet locales.

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.