IF, IFS, AND & OR

Many sheets need to make a decision for each row: pass or fail, discount or no discount, on time or late. The IF function makes those decisions, and AND, OR and IFS let you write rules with several conditions without getting lost in brackets.

In this lesson you will write IF formulas that return text, numbers and calculations, combine conditions, build a grade column with IFS, and learn the older nested-IF style you will still see in office files.

The IF function

=IF(logical_test, value_if_true, value_if_false)

• logical_test is a comparison that is TRUE or FALSE, such as B2>=33.
• value_if_true is what the cell shows when the test is TRUE.
• value_if_false is what it shows when the test is FALSE.

Here is a Class 10 result sheet from a school in Rohtak, with the passing mark of 33 used by many Indian boards:

Column D holds =AVERAGE(B2:C2). To mark a pass or fail in Maths alone:

=IF(B2>=33,"Pass","Fail")

Aarav: Pass
Kabir: Fail

Text results go in double quotes. Numbers and formulas do not need quotes, so IF can also calculate. A shop gives 10% off on bills of ₹2,000 or more, with the bill amount in B2:

=IF(B2>=2000, B2*10%, 0)

For a bill of 2500 this returns 250; for 1800 it returns 0. To leave a cell looking empty instead, use "" (two double quotes with nothing between them) as the result.

AND, OR and NOT

A student passes only if they pass both subjects. AND returns TRUE only when every condition is TRUE:

=IF(AND(B2>=33, C2>=33), "Pass", "Fail")

Aarav: Pass
Priya: Pass
Kabir: Fail
Meera: Fail
Rohan: Pass

Kabir failed Maths and Meera failed Science, so both get "Fail" even though Meera's average is higher than Kabir's.

OR returns TRUE when at least one condition is TRUE. A bakery delivers free in Pune and Mumbai, with the city in C2:

=IF(OR(C2="Pune", C2="Mumbai"), "Free delivery", "₹60 delivery")

NOT flips TRUE and FALSE: =NOT(C2="Pune") is TRUE for every city except Pune. You will often see the same idea written with <>, as in C2<>"Pune".

IFS: several outcomes without nesting

Grades need more than two outcomes. This example uses a simple school rule, not any particular board's scheme: 90 and above is A, 75 and above is B, 60 and above is C, 33 and above is D, and below 33 is "Needs support".

IFS (Excel 2019 and later, and Google Sheets) takes pairs of test and result and returns the result of the first test that is TRUE:

=IFS(D2>=90,"A", D2>=75,"B", D2>=60,"C", D2>=33,"D", TRUE,"Needs support")

Aarav (84.5): B
Priya (91.5): A
Kabir (38): D
Meera (46.5): D
Rohan (66): C

Two details make this work:

• The tests run from the highest boundary down. If D2>=33 came first, every passing student would get D.
• IFS has no "otherwise" argument. The last pair, TRUE,"Needs support", acts as the otherwise, because TRUE is always true. Without it, a value that matches no test gives #N/A.

SWITCH: match exact values

When you compare one cell with a list of exact values, SWITCH is shorter than IFS. A shop records the payment mode as a code in D2:

=SWITCH(D2, "U","UPI", "C","Cash", "K","Card", "Unknown")

The first argument is the value to check, then pairs of "if it equals this, return that". The last single argument is the default. A code of "U" returns UPI, and a typo such as "X" returns Unknown.

IFERROR: a clean result instead of an error

Formulas sometimes produce errors that are not really problems, such as #DIV/0! when a new row has no data yet. IFERROR returns its second argument when the first one produces any error:

=IFERROR(B2/C2, "")

Use it carefully. It hides every error, including real mistakes such as a misspelt range name. Apply it only once a formula is known to be correct.

Common mistakes

• Wrong order in IFS. Test the highest boundary first, or the first broad test catches everyone.
• Forgetting quotes around text. =IF(B2>=33,Pass,Fail) gives #NAME?.
• Writing B2>=33 AND C2>=33. Excel has no AND operator; use the function AND(B2>=33, C2>=33).
• Using > instead of >=. A student with exactly 33 fails if you write B2>33. Read the rule carefully.
• Wrapping everything in IFERROR. It hides real errors along with expected ones.

Key points

• IF(test, if_true, if_false) returns one of two results; text results need quotes.
• AND needs every condition to be TRUE, OR needs at least one, and NOT flips the result.
• IFS checks pairs in order and returns the first match; end with TRUE, result as an otherwise.
• Nested IF is the older way to get several outcomes; it still works but is harder to read.
• SWITCH matches exact values, and IFERROR replaces any error with a value you choose.

=IFS(B2<=100, B2*3, B2<=300, B2*5, TRUE, B2*7) + IF(C2="Commercial", 50, 0)

80 units, Domestic: 240
250 units, Commercial: 1300
420 units, Domestic: 2940