Mastering the Excel IF Function with Multiple Conditions

1. Understanding the Basics of IF

The IF function evaluates a single logical test and returns one value if the test is TRUE and another if it is FALSE. Its syntax is identical in Excel and Google Sheets:

IF(logical_test, value_if_true, value_if_false)

Simple example

Formula in B2

=IF(A2>10, "High", "Low")

Result

Key points

• A single IF handles only one condition. To test several criteria you must nest more IFs or combine logical functions (AND, OR).
• The function works in all modern Excel versions (365, 2021, 2019) and in Google Sheets.
• Text results need double quotes; numbers do not.

2. Combining IF with AND / OR for Multiple Conditions

When you need to evaluate several tests at once, wrap them in AND (all must be true) or OR (any can be true) and feed the result to IF.

Basic patterns

=IF(AND(condition1, condition2, …), value_if_true, value_if_false)
=IF(OR(condition1, condition2, …), value_if_true, value_if_false)

Both functions are available in Excel 2021/365 and Google Sheets.

Example 1 – Bonus eligibility (all criteria must be met)

Formula

=IF(AND(B2>=100000, C2>=2), "Yes", "No")

Result

Only Alice satisfies both the sales threshold (≥ 100 000) and the tenure requirement (≥ 2 years).

Example 2 – Discount tier (any condition true)

Formula

=IF(OR(B2>=5000, C2="Gold"), "10%", "0%")

Result

3. Nesting IF Statements for Tiered Logic

When you have more than two possible outcomes, embed additional IFs inside the true/false parts of the first test. This creates a decision tree that evaluates conditions sequentially.

Typical structure

=IF(A2>90, "Excellent",
    IF(A2>75, "Good",
        IF(A2>60, "Pass", "Fail")))

Sample data

The formula returns the first label whose condition is satisfied, then stops evaluating.

Readability tips

• In Excel 365/2021 you can press Alt+Enter inside the formula bar to add line breaks, making each level clear.
• Use indentation (as shown) to visually separate the nested levels.

4. Using IFS for Cleaner Multi‑Condition Logic

IFS (available in Excel 2016/365 and Google Sheets) eliminates deep nesting by evaluating a list of condition → result pairs and returning the first true result.

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

Sample data

Why prefer IFS?

1. Readability – each condition sits on its own line.
2. No extra parentheses – the formula is compact.
3. Built‑in default – adding TRUE, <default> handles any unmatched case without an extra IFERROR.

Compatibility: IFS works in Excel 2016 onward (including 365/2021) and in Google Sheets.

5. Modern Alternatives: XLOOKUP and FILTER

For many “lookup‑based” decisions, XLOOKUP (Excel 2021/365) or FILTER (Excel 365, Google Sheets) can replace complex IF trees.

Example data

XLOOKUP (Excel 365)

=XLOOKUP("Apple", IF(B2:B4="In Stock", A2:A4), C2:C4, "Not Available")

Result: 1.20

FILTER (Google Sheets)

=FILTER(C2:C4, A2:A4="Apple", B2:B4="In Stock")

Result: 1.20

Both approaches keep the logic in a single, readable expression.

6. Best Practices & Common Pitfalls

Frequent pitfalls

1. Missing parentheses – a stray or missing ) turns a valid IF into a syntax error.
2. Hard‑coding text without quotes – Excel treats unquoted words as named ranges and returns #NAME?.
3. Assuming unlimited nesting – older Excel versions (pre‑2007) limit nesting to 7 levels; Excel 365 and Google Sheets have effectively no practical limit.

7. Real‑World Templates You Can Copy

Template 1 – Tiered discount (10 % if sales ≥ $5,000 and tier = “Gold”)

Formula (both Excel & Google Sheets)

=IF(AND(B2>=5000, C2="Gold"), 0.10, 0)

Result – 0.10 for rows A and C; 0 for B and D.

Template 2 – Pass/Fail with three criteria

Formula

=IF(AND(B2>=70, C2>=80, D2=0), "Pass", "Fail")

Result – Emma = Pass, Liam = Fail (attendance < 80), Noah = Fail (disciplinary flag).

Template 3 – Lookup price only for stocked items

Formula (Excel 365)

=XLOOKUP("Pen", IF(B2:B4="In Stock", A2:A4), C2:C4, "Not Available")

Result – 1.50.

8. FAQ

How do I combine IF with AND in Excel?

Wrap the multiple tests inside AND and place that inside the IF’s logical test, e.g., =IF(AND(A2>0, B2="Yes"), "OK", "Check"). The AND returns TRUE only when all conditions are met, which then triggers the value_if_true.

What’s the difference between nested IF and IFS?

Nested IFs embed one IF inside another, creating a deep parentheses chain; IFS evaluates a list of condition/result pairs and stops at the first true condition. IFS is generally more readable and requires fewer parentheses, but it’s unavailable in Excel versions earlier than 2016.

Can I use XLOOKUP for multiple conditions?

Yes. By nesting an IF (or FILTER) inside the lookup array you can limit the search to rows that meet additional criteria, e.g., =XLOOKUP(A2, IF(StatusRange="In Stock", LookupRange), ReturnRange, "N/A"). This works in Excel 2021/365; older versions need INDEX/MATCH combos.

Is there a limit to the number of conditions in an IF formula?

In modern Excel (365/2021) and Google Sheets there’s effectively no hard limit; the only practical constraints are file size and performance. Older Excel versions capped nested IFs at 7 levels, but you can still use AND/OR to combine many tests within a single IF.

How to avoid the #VALUE! error when using multiple IFs?

#VALUE! usually appears when you compare mismatched data types (e.g., a number stored as text). Coerce values with VALUE() for numbers, TEXT() for strings, or use the double‑unary -- operator (--A2). Also, ensure every IF has a matching closing parenthesis.

All formulas shown are syntactically valid for the indicated platforms. Adjust cell references as needed for your own worksheets.