Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 29 ยท Formulas and Functions

IF, AND, OR and IFS

Make Google Sheets decide with IF, nested IF, IFS, SWITCH, AND, OR, NOT and IFERROR. Learn how conditions work, with live stand-ins to try.

Until now your formulas have calculated. Logical functions let a sheet decide: mark an order as "Late", give a discount only above a threshold, show "Pass" or "Fail", or hide an ugly error. The tool for all of this is the humble IF, plus helpers that combine several conditions.

Every decision starts with a condition: something that is either TRUE or FALSE, such as B2 > 100 or A2 = "Paid". This lesson shows how to write conditions, how to branch with IF, IFS and SWITCH, how to combine conditions with AND, OR and NOT, and how to catch errors with IFERROR.

Every decision is TRUE or FALSE

Condition B2 > 100 gives TRUE or FALSE.
IF Pick one of two results.
IFS Test many conditions in order.
IFERROR Show a fallback on errors.
1 / 2

Conditions and comparison operators

A condition compares two values with one of these operators: = (equal), <> (not equal), >, <, >= and <=. Typing =B2>100 into a cell shows TRUE or FALSE. Text comparisons ignore capitalization, so ="Paid"="paid" is TRUE. Text goes in double quotes; numbers and cell references do not.

Comparing with an empty cell is a special case: =A2="" is TRUE for a blank cell, which is the standard way to test for "nothing here".

IF: choose between two results

IF(condition, value_if_true, [value_if_false]):

TEXT
=IF(B2>=50, "Pass", "Fail")
=IF(C2="", "Missing", C2)
=IF(D2>1000, D2*0.9, D2)

The results can be text, numbers, cell references or even other formulas. If you omit the last argument and the condition is FALSE, the cell shows FALSE, which is rarely what you want; write "" for "show nothing" instead.

The Sheets app cannot run on this page, so the formulas are read-only. The TryIt blocks are JavaScript stand-ins: small functions that follow the same logic, so you can change the numbers and see the result.

JS Stand-in: IF over a column

Press Run (or Ctrl + Enter) to see the console output here.

No output.

AND, OR and NOT

One condition is often not enough. Combine them:

  • AND(a, b, ...) is TRUE only when all are true.
  • OR(a, b, ...) is TRUE when at least one is true.
  • NOT(a) flips TRUE to FALSE and back.
TEXT
=IF(AND(B2>=50, C2>=50), "Pass", "Retake")
=IF(OR(D2="VIP", E2>500), "Free shipping", "Standard")
=IF(NOT(ISBLANK(F2)), "Filled in", "Empty")

Unlike a programming language, you do not write B2>=50 AND C2>=50 between conditions: AND and OR are functions that wrap their conditions. There are also the * and + tricks (multiplying conditions acts like AND, adding like OR), which you will meet in Array Formulas and ARRAYFORMULA.

Nested IF and why to avoid long chains

An IF can sit inside another IF to choose among more than two outcomes:

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

Sheets checks the conditions from left to right and stops at the first TRUE, so order matters: testing B2>=70 first would label a 95 as "C". Past three or four levels, nested IF becomes hard to read and easy to break.

IFS: many conditions without nesting

IFS(condition1, result1, condition2, result2, ...) does the same chain more cleanly:

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

Notice the last pair: TRUE, "F" acts as the "otherwise" branch. Without it, a value that matches nothing returns the error #N/A. IFS has no built-in else, so always add a catch-all unless you want the error.

JS Stand-in: IFS picks the first TRUE condition

Press Run (or Ctrl + Enter) to see the console output here.

No output.

SWITCH: match one value against a list

When you compare one value with many exact options, SWITCH reads better than IFS:

TEXT
=SWITCH(A2, "S", "Small", "M", "Medium", "L", "Large", "Unknown size")

The expression comes first, then value/result pairs. A final single value is the default. Use IFS for ranges and inequalities (>=80) and SWITCH for exact matches. For long lists of codes, a lookup table beats both; see VLOOKUP, HLOOKUP and Their Limits.

IFERROR: handle errors gracefully

IFERROR(value, value_if_error) returns the first argument unless it is an error, in which case it returns the second:

TEXT
=IFERROR(B2/C2, 0)
=IFERROR(VLOOKUP(A2, Prices!A:B, 2, FALSE), "Not found")

It is handy for division by a blank or a lookup that finds nothing. But it hides every kind of error, including typos in your own formula. Wrap only the part that can legitimately fail, and prefer a visible message like "Not found" over 0 where a zero could be mistaken for real data. Understanding Formula Errors covers each error type. IFNA is a narrower sibling that catches only #N/A, which makes it safer for lookups.

JS Stand-in: IFERROR and a safe division

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In real Sheets the division by zero gives #DIV/0! rather than Infinity; the stand-in treats a non-finite result as that error.

A complete example

An order sheet has the amount in B2, the customer type in C2 and the status in D2. Shipping is free for VIPs or orders over 100, but never for cancelled orders:

TEXT
=IF(D2="Cancelled", "-", IF(OR(C2="VIP", B2>100), "Free", 5))

Read it from the outside: cancelled orders get a dash; otherwise OR decides between free and a flat 5. Test with a few rows covering each branch before copying the formula down. Conditions like these also drive highlighting in Conditional Formatting.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does =IF(B2>10, "High") show when B2 is 5?

    With no value_if_false argument, IF returns FALSE when the condition is not met.
  2. 2. Which formula is TRUE only when both A1 and B1 are greater than 0?

    AND is a function that returns TRUE only if every condition is true. The last option is not valid Sheets syntax.
  3. 3. What is the purpose of the final pair TRUE, "F" in an IFS formula?

    IFS has no else branch. A condition that is always TRUE at the end gives a default instead of #N/A.
  4. 4. Why should you be careful with IFERROR around a long formula?

    IFERROR returns the fallback for any error, so a typo or wrong range can go unnoticed.

Summary

  • A condition compares values and gives TRUE or FALSE; text is compared without regard to case.
  • IF(condition, yes, no) branches; write "" if you want nothing shown for the false case.
  • AND, OR and NOT combine conditions; they are functions, not words between conditions.
  • IFS replaces deep nesting and needs a final TRUE pair as a default; SWITCH matches one value against exact options.
  • IFERROR and IFNA provide fallbacks but can hide real mistakes.

Next: Understanding Formula Errors