Google Sheets IFS Function: Syntax, Examples, and Tips

Use IFS in Google Sheets to test conditions in order and return the value for the first condition that is true. For example, with a student’s score in A2, this formula assigns a letter grade: =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F"). It returns B for a score of 84. Before you begin, decide which conditions to check and put the most specific or highest-priority condition first.

What You’ll Need

A Google Sheets spreadsheet open in a web browser.
Some data to evaluate, such as scores, sales amounts, dates, or status labels. A small sample row is useful for testing.
A clear list of the conditions you want to check and the result each condition should return.

The examples below use commas between formula arguments, which is the default in many spreadsheet locales. If your Google Sheets locale expects semicolons, replace the argument-separating commas with semicolons when entering a formula. IFS is also available in Excel, but this guide uses Google Sheets in a browser.

Step-by-Step

Arrange the data and decide where results go. Keep the value you want to test in a consistent column, and reserve a neighboring column for the result. For instance, put scores in column A and grades in column B, with the first data row at row 2. Clear headers such as “Score” and “Grade” make it easier to check that the formula refers to the intended cells. IFS evaluates one row at a time unless you build a separate array formula.
Write down the rules in the order they should apply. List each test and its corresponding output. For grades, the rules might be 90 or higher returns A, 80 or higher returns B, then 70 or higher returns C, and so on. Order matters because IFS stops at the first true condition; it does not continue looking for a later, more appropriate match. With descending score thresholds, put the largest threshold first.
Select the first result cell. Click B2 in the grade example. Enter formulas in the formula bar or directly into the selected cell, then press Enter. A formula generally begins with an equals sign. The IFS structure alternates between a condition and the value to return when that condition is true. Conditions can use comparisons such as greater than, equal to, or not equal to.
Enter a formula with paired conditions and results. With the score in A2 and the grade result in B2, enter this formula. The last condition, TRUE, acts as a catch-all, so every score that did not meet an earlier threshold receives F. For a score of 84, the first two tests are false and the third test is true, so the result is B.
With the student’s score in A2, use this formula in B2: =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")
Check that the first match is the intended one. Test a value near each boundary, not just a typical value. In the grade example, check 90, 89, 80, and 79. A score of 90 should return A; 89 should return B; 80 should return B; and 79 should return C. This catches errors such as a missing equals sign or thresholds entered in the wrong order. You can temporarily type a test value in A2, then restore the original data.
Copy the formula to other rows. When the first result is correct, select B2 and use the small blue fill handle at the lower-right corner of the cell to drag the formula down. You can also copy B2, select the destination cells, and paste. Google Sheets adjusts a reference such as A2 to A3 in the next row automatically. Inspect a few copied formulas or results to confirm that each row is using its own input value.
Add a deliberate default for values outside your rules. A catch-all condition can return a helpful label instead of leaving the formula without a match. For example, if you categorize order totals in B2, the final TRUE handles amounts below the other thresholds. An order total of 75 returns “Reduced shipping.”
With an order total in B2, enter this formula in the cell where you want the category: =IFS(B2>=100,"Free shipping",B2>=50,"Reduced shipping",TRUE,"Standard shipping")
Combine conditions when one rule needs more than one test. Use AND when every part of a rule must be true, or OR when any part may be true. For example, suppose the order total is in B2 and the customer’s membership status is in C2. The formula below returns Priority only when the total is at least 80 and the status is “Yes”; otherwise, it returns Standard. Check that the text in C2 is spelled consistently with the text in the formula.
With the total in B2 and membership status in C2, enter this formula in the result cell: =IFS(AND(B2>=80,C2="Yes"),"Priority",TRUE,"Standard")

Google Sheets recalculates the result when a referenced cell changes. You do not need to rerun the formula manually. If you edit the rules, review the ordering and test the changed thresholds again, especially where two ranges meet.

Common Mistakes

Putting overlapping conditions in the wrong order. If you check “score is at least 70” before “score is at least 90,” a score of 95 matches the first test and never reaches the A rule. Place higher thresholds first when using descending cutoffs.
Leaving out the fallback condition. When none of the listed conditions is true, IFS returns an #N/A error. Add a final TRUE and a sensible result if every possible input should receive a category. If an unmatched value should be flagged, a label such as “Check value” may be safer than an assumed category.
Forgetting that conditions and results come in pairs. Each condition needs a value to return. Review the formula from left to right and make sure every condition is followed by its intended result, including the final catch-all pair if you use one.
Using text that does not match the source data. A rule checking for “Paid” will not match a cell containing “Payment” or an accidental extra space. Check the original values, and standardize categories if they vary.
Comparing numbers stored as text. A value that looks like 75 may be stored as text after an import or manual entry. Numeric comparisons can then behave unexpectedly. Check the cell format and input data, and convert text values to numbers when appropriate.
Using the wrong separator for the spreadsheet locale. If Sheets reports a formula parse error, your locale may require semicolons rather than commas between arguments. Change separators consistently; do not replace commas that are part of quoted text.

Tips

Keep the rule list readable. Put one condition-result pair on each line while editing a long formula in the formula bar. Line breaks can make a formula easier to inspect, but keep the commas and parentheses in the right places. A compact formula is not necessarily easier to maintain.
Use a clear fallback. A catch-all such as “Other” is suitable when all remaining cases belong together. If unexpected inputs need attention, use a label like “Review” so they are visible instead of silently grouped with normal cases.
Use AND and OR for combined logic. AND requires all its listed tests to be true; OR requires at least one. Keep complex conditions simple enough to explain to another person, and test examples where one part of a combined condition changes while the rest stays the same.
Choose IFS for ordered rules, not every kind of lookup. It is convenient when you have a manageable set of conditions and return values. If you are matching many entries in a maintained table, a lookup approach may be easier to update than a very long IFS formula.
Remember that IFS returns one result. It returns the value paired with the first true condition; it does not list all matching conditions. If multiple rules should contribute separate outputs, set up a different calculation rather than expecting IFS to return them all.

Troubleshooting

You see #N/A. Check whether every condition is false for the current input. If a result is needed for every possible input, add a final TRUE condition and a default result. If #N/A is intentional, make sure the input really is outside the defined categories rather than a typo or unexpected blank.

You see a formula parse error. Check for a missing opening or closing parenthesis, a missing comma between arguments, unpaired quotation marks around text, or a condition without a result. If the formula otherwise looks correct, check the spreadsheet locale and try the separator it expects. Use straight quotation marks in formula text.

The result is a category you did not expect. Evaluate the conditions from left to right using the current cell value. The first true condition wins. Reorder overlapping rules, correct a threshold, or revise the comparison operator. Then test values immediately below, at, and above each boundary.

A row shows a result that seems unrelated to its input. Confirm that the formula references the same row as the result. After copying down, the formula in B3 should normally refer to A3, not keep referring to A2. Also check that the source cell contains a real number, date, or expected text value rather than an imported text string.

Some cells are blank or inconsistent. Decide explicitly how blanks should be handled. A blank may behave like an empty or zero-like value in some comparisons, which can send it into a category you did not intend. Add a blank check before the other rules if blank inputs need a distinct output, and test it with an actually empty cell. If the data contains spaces, clean or standardize it before relying on exact text comparisons.

Frequently Asked Questions

What does IFS do in Google Sheets?
IFS checks conditions in the order you provide and returns the result paired with the first condition that is true. It is a way to express several ordered if/then rules in one formula.

What happens if none of the conditions is true?
Google Sheets returns #N/A when no condition matches. Add a final TRUE condition paired with a default result if you want the formula to return a category for all other cases.

Can I use IFS with text or dates?
Yes. Conditions can compare text or dates as well as numbers. For text, ensure the value matches the category you intend to test; for dates, make sure the cells contain actual dates rather than date-like text.

Can IFS check multiple things at once?
Yes. Put functions such as AND or OR inside a condition when a rule depends on multiple tests. AND requires every test to be true, while OR requires at least one to be true.

Is IFS the same in Google Sheets and Excel?
The core purpose is similar: both have an IFS function that returns the result for the first true condition. This guide’s steps and browser interface refer to Google Sheets; formula separators can depend on locale in either spreadsheet program.

How many conditions can I put in IFS?
You can include multiple condition-result pairs, but a very long formula can be difficult to review and maintain. If rules grow substantially, consider organizing them in a separate table and using a suitable lookup method.

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.