Excel IF, AND, OR and IFS Functions Explained

Logical functions in Excel test a condition and then either return TRUE or FALSE, or choose between results. The main ones are IF, AND, OR, NOT, IFS, SWITCH and the error handlers IFERROR and IFNA. IF is the one you will use most: =IF(condition, value_if_true, value_if_false) gives one answer when the condition is true and another when it is not.

This guide goes from a single comparison to nested IF, to combining AND with OR (where the placement of brackets changes the answer), and to the small details that make a correct-looking formula give a wrong result. Every result was produced by running the formula in Microsoft Excel.

Comparisons and TRUE or FALSE

Every logical function starts from a comparison, which returns TRUE or FALSE. The operators are =, <> (not equal), >, <, >= and <=. A1 holds 15 and B1 holds the text Pune:

The IF function

IF takes a condition and two results. Text results go in double quotes. In column C every mark of 40 or more passes. Column D leaves out the value for a failed test:

What the sheet shows

What is inside the cells (Show Formulas)

Look at D6: when the test fails and there is no value_if_false, Excel returns the word FALSE, which is rarely what you want in a report. Always give both results, even if the second is an empty text "".

Nested IF: several outcomes

When there are more than two outcomes, put another IF in the value_if_false place. Excel checks the conditions in order and stops at the first one that is true. That is why the order matters. Column C tests the highest band first; column D puts the lowest band first; column E uses > instead of >=:

Two rules come out of this. Order the tests from the strictest to the loosest (or the other way round with <), and decide whether a boundary value belongs to the upper or the lower band, then pick >= or > to match.

AND, OR and NOT

AND(cond1, cond2, ...) is TRUE only if all conditions are true. OR(...) is TRUE if at least one is true. NOT(cond) flips TRUE and FALSE. They usually sit inside an IF:

What the sheet shows

What is inside the cells

Ravi (35 in Maths, 90 in Science) fails the AND rule but passes the OR rule. John fails both.

AND with an OR inside: where the brackets go

Real rules combine both: “call the lead if the city is Pune and either the order is at least 500 or the person is a member”. Condition A must always hold, and condition B is one of two alternatives, so the OR goes inside the AND:

=IF(AND(B2="Pune", OR(C2>=500, D2="Yes")), "Call", "Skip")

Move the brackets and the rule changes. Column F below is OR(AND(city is Pune, order at least 500), member), which means “Pune with a big order, or any member”. It looks almost the same and gives different answers for John and Zoya, who are members outside Pune:

The rule for the brackets: whatever is inside OR( ) is one alternative choice for a single slot of the AND. Say the rule out loud in words first, then place the brackets to match the words. The same logic appears in other tools: in FILTER it is (city="Pune")*((order>=500)+(member="Yes")), where * means AND and + means OR.

IFS and SWITCH

IFS(test1, result1, test2, result2, ...) checks the tests in order and returns the result of the first true one. It avoids the nested brackets. It exists in Excel 2019 and Microsoft 365, not in Excel 2016 or earlier. If nothing matches, IFS returns #N/A, so end with TRUE as a catch-all:

SWITCH(value, match1, result1, match2, result2, ..., default) compares one value with a list of exact matches, which suits codes and labels:

IFERROR and IFNA

IFERROR(value, if_error) replaces any error with a value you choose. IFNA(value, if_na) replaces only #N/A, the error that lookups give when nothing is found. Prefer IFNA when you expect only that case, because IFERROR also hides real mistakes such as a wrong range:

What Excel evaluates and what it skips

IF only evaluates the branch it picks, so it can guard against an error in the other branch. AND and OR evaluate every argument, so an error in a later argument spoils the answer even if an earlier one already decided it. A1 holds 0:

Gotchas with comparisons

A1 holds 15, A2 holds the text 100, A3 holds the number 100 and A4 is empty:

When nested IF gets too long

Excel allows up to 64 levels of nesting, but nobody should read that. If the bands are a list, put them in a table and let a lookup pick the band. Here the four bands are written as an array constant inside VLOOKUP with approximate matching (the last argument TRUE), which finds the largest lower limit that does not exceed the mark. In a real workbook you would put the band table in cells and point at it:

What the sheet shows

What is inside the cells (Show Formulas)

The lookup functions are covered in detail in the lookup guide of this series.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution.

1. Commission is 10% for sales of 10,000 or more, 5% for 5,000 or more, and 2% otherwise. Write one formula for the first row and check it on 12,000, 5,000 and 4,999.

Show solution

What the sheet shows

	A	B	C
1	Rep	Sales	Commission rate
2	Asha	12000	10%
3	Ravi	5000	5%
4	Meera	4999	2%

What is inside the cells (Show Formulas)

	A	B	C
1	Rep	Sales	Commission rate
2	Asha	12000	=IF(B2>=10000,0.1,IF(B2>=5000,0.05,0.02))
3	Ravi	5000	=IF(B3>=10000,0.1,IF(B3>=5000,0.05,0.02))
4	Meera	4999	=IF(B4>=10000,0.1,IF(B4>=5000,0.05,0.02))

Test the highest band first. 5,000 gets 5% because the test is >=, and 4,999 drops to 2%.

2. Write a test that is TRUE only when A1 is between 18 and 60 inclusive, and check it on 18, 60, 61 and 17.

Show solution

Testing 18, 60, 61 and 17 in A1 to A4

Formula	Excel shows
=AND(A1>=18,A1<=60)	TRUE
=AND(A2>=18,A2<=60)	TRUE
=AND(A3>=18,A3<=60)	FALSE
=AND(A4>=18,A4<=60)	FALSE

Use AND with two comparisons. The shortcut 18<=A1<=60 does not work in Excel.

3. A1 holds 35. What does =IF(A1>=40,"Pass") return?

Show solution

A1 holds 35

Formula	Excel shows
=IF(A1>=40,"Pass")	FALSE

With no value_if_false, IF returns FALSE, not an empty cell.

4. What do =OR(TRUE,1/0) and =IF(TRUE,1,1/0) return?

Show solution

Two formulas with a division by zero

Formula	Excel shows
=OR(TRUE,1/0)	#DIV/0!
=IF(TRUE,1,1/0)	1

OR evaluates all its arguments, so the division fails. IF only evaluates the branch it chooses.

Frequently asked questions

What is the IF function in Excel?

IF(logical_test, value_if_true, value_if_false) tests a condition and returns one result if it is TRUE and another if it is FALSE.

How do I use IF with AND and OR in Excel?

Put AND(...) or OR(...) as the first argument: =IF(AND(B2>=40, C2>=40), "Pass", "Fail"). To combine them, nest one inside the other, for example AND(A1, OR(B1, C1)).

How many IF statements can you nest in Excel?

Up to 64 levels in modern versions of Excel, but readable formulas rarely go beyond three or four. Use IFS, SWITCH or a lookup table instead.

What is the difference between IF and IFS?

IF handles two outcomes and needs nesting for more. IFS takes pairs of test and result and returns the first true one, without nesting. IFS is available in Excel 2019 and Microsoft 365.

Why does my IF formula return FALSE?

Usually because the test failed and the formula has no value for that case: =IF(A1>=40,"Pass"). Add a third argument.

Why does IF give the wrong grade in a nested formula?

Excel stops at the first condition that is true. If the loosest condition comes first, everyone matches it. Test the strictest condition first.

What is the difference between IFERROR and IFNA?

IFERROR catches every error type. IFNA catches only #N/A, so it does not hide mistakes such as #DIV/0! or #REF!.

Is text comparison in Excel case sensitive?

No. ="pune"="PUNE" is TRUE. Use EXACT(a, b) when you need a case-sensitive comparison.