IF AND Generator
Return one value only when every condition is true, using IF with a nested AND.
IF AND
Return one value only when every condition is true, using IF with a nested AND.
A comparison that returns TRUE or FALSE, for example B2>=100.
The second test. Text you compare against needs quotes, for example C2="Paid".
Returned only when every condition is true. Text needs quotes.
Returned when at least one condition is false. Text needs quotes.
=IF(AND(B2>=100, C2="Paid"), "Approved", "Review")Worked Example
Flag an order as ready to ship only when the amount is at least 100 and the invoice status is Paid.
=IF(AND(B2>=100, C2="Paid"), "Approved", "Review")Returns: Returns "Approved" when B2 is 100 or more AND C2 reads Paid; returns "Review" if either test fails.
Checks Before You Paste
- •AND is a function, not an operator: the SQL-style B2>=100 AND C2="Paid" is not valid spreadsheet syntax, and neither is &&. Every test has to sit inside AND(...) separated by commas.
- •Never chain comparisons like 10<A2<20. Excel and Google Sheets evaluate that left to right, so 10<A2 collapses to TRUE or FALSE and then gets compared against 20 - which is not the range test you meant. Write AND(A2>10, A2<20) instead.
- •Text comparisons are case-insensitive, so C2="paid" also matches "Paid", and a blank cell satisfies both C2="" and C2=0. Use EXACT(C2,"Paid") for case-sensitive checks and add C2<>"" when blanks must not count.
How IF AND works
Availability: IF and AND have both shipped in every version of Excel, on Windows, Mac and the web, and behave the same in Google Sheets and LibreOffice Calc. AND takes up to 255 arguments from Excel 2007; Excel 2003 stopped at 30.
AND is a function, not an operator. It takes a list of tests and returns a single TRUE only when every one of them is TRUE. Wrapping it in IF - =IF(AND(test1, test2), value_if_true, value_if_false) - is how a spreadsheet says "do this only when all of these hold". The SQL-style B2>=100 AND C2="Paid" is not valid in Excel, and neither is && from programming languages; every test has to sit inside AND's brackets, separated by commas.
The behaviour that surprises programmers is that AND does not short-circuit. It evaluates all of its arguments before returning anything, so a guard condition does not protect the argument that follows it. =IF(AND(B2<>0, A2/B2>10), "High", "Low") still returns #DIV/0! when B2 is zero, because AND computes A2/B2 regardless of the first test's result. Protection has to come from nesting IFs instead: =IF(B2=0, "", IF(A2/B2>10, "High", "Low")). IF itself does short-circuit - only the branch taken is calculated.
AND is strict about what it is given directly. A number is read as a condition, with 0 meaning FALSE and anything else TRUE, but a text value passed straight in returns #VALUE!, so =AND(B2, C2="Paid") breaks the moment B2 holds a word. Text and empty cells inside a range argument are a different case - those are ignored, and a range containing no logical values at all returns #VALUE! because there was nothing to test.
Comparisons inside AND ignore case, so C2="paid" also matches "Paid" and "PAID"; use EXACT when case matters. A blank cell satisfies both C2="" and C2=0 and is smaller than every number, so a test like B2<100 passes on rows nobody has filled in yet - add B2<>"" as its own argument when blanks must not qualify. Multiplication is an equivalent to AND that many people prefer inside array formulas: (B2>=100)*(C2="Paid") is 1 only when both hold, and it coerces to 1 and 0 rather than TRUE and FALSE.
Syntax
=IF(AND(logical1, logical2, ...), value_if_true, value_if_false)| Argument | Required | What it does |
|---|---|---|
logical1 | Required | The first test AND evaluates. A comparison, a boolean-returning function, or a number where 0 is FALSE. Text passed in directly returns #VALUE!. |
logical2, ... | Optional | Up to 254 further tests. Every one is evaluated whatever the earlier ones returned, so none of them can guard the next against an error. |
value_if_true | Optional | Returned when AND is TRUE, meaning every test passed. Quote text; a formula here only runs when this branch is taken. |
value_if_false | Optional | Returned when any test failed. Omit the argument entirely and a failure returns the word FALSE; leave a trailing comma and it returns 0. |
More worked examples
Transaction dates in A2:A2000, labelling the rows that fall inside the first quarter of 2026.
=IF(AND(A2>=DATE(2026,1,1), A2<=DATE(2026,3,31)), "Q1", "Other")Returns: "Q1" on rows dated between 1 January and 31 March 2026 inclusive, "Other" elsewhere.
This is the correct way to write a between test. =IF(DATE(2026,1,1)<=A2<=DATE(2026,3,31), ...) is accepted by Excel and is always wrong.
A discount check where the order value is in B2, the customer tier in C2, and both thresholds live in F1 and F2 so they can be changed without editing formulas.
=IF(AND(B2<>"", B2>=$F$1, C2=$F$2), "Eligible", "")Returns: "Eligible" only on rows with a value entered, at or above the threshold, in the named tier.
The B2<>"" test comes first for readability, not for safety - AND evaluates all three arguments either way. It is there because a blank cell would otherwise fail B2>=$F$1 quietly rather than obviously.
A subscriber list where an account must be active and in one of two countries to count as domestic, with status in D2 and country code in E2.
=IF(AND(D2="Active", OR(E2="UK", E2="IE")), "Domestic", "Overseas")Returns: "Domestic" for active UK or Irish accounts, "Overseas" for everything else.
Nesting OR inside AND is how mixed logic is written. There is no precedence to remember because the brackets state it explicitly.
Common mistakes
- Expecting a guard argument to prevent an error
- AND evaluates every argument before it returns, so =IF(AND(B2<>0, A2/B2>10), ...) still produces #DIV/0! when B2 is zero. Only nested IFs give real protection: =IF(B2=0, "", IF(A2/B2>10, ...)).
- Chaining comparisons
- =IF(AND(10<A2<20), ...) and =IF(10<A2<20, ...) both evaluate 10<A2 first, get TRUE or FALSE, then compare that to 20. The result is almost always FALSE and never an error. Write AND(A2>10, A2<20).
- Passing text straight into AND
- =AND(B2, C2="Paid") returns #VALUE! as soon as B2 contains a word rather than a number or a boolean. Make it an actual test: AND(B2<>"", C2="Paid").
- AND over a range collapsing to one answer
- =IF(AND(B2:B100>0), "All positive", "Not all") returns a single TRUE or FALSE for the whole column, not a result per row. That is correct behaviour and rarely what was wanted - to test each row, put the formula on each row.
- Blanks quietly satisfying a numeric test
- An empty cell reads as 0, so it passes B2<100 and any "below threshold" rule. Every unfilled row gets flagged. Add B2<>"" as a separate argument, which is one of the few cases where the extra test genuinely changes the outcome.
Frequently Asked Questions
No. Unlike && in most programming languages, AND evaluates all of its arguments and then decides. This matters whenever a later argument can raise an error - the error surfaces even though the earlier test would have ruled the row out. IF is the function that skips the branch it does not take.
255 in Excel 2007 and later, and in Google Sheets; 30 in Excel 2003. Long before that becomes a limit the formula becomes unreadable - once you are past four or five tests, a helper column per rule, or a COUNTIFS that counts how many rules a row satisfies, is easier to debug.
Yes: (B2>=100)*(C2="Paid") returns 1 when both hold and 0 otherwise, and IF treats a non-zero number as TRUE. It is the standard form inside SUMPRODUCT and array formulas. The difference is in edge cases - multiplication raises #VALUE! on text, where AND ignores text found inside a range argument.
Nest one inside the other and let the brackets set the precedence: =IF(AND(D2="Active", OR(E2="UK", E2="IE")), ...) requires active status plus either country. Excel has no operator precedence between AND and OR to memorise, because they are functions rather than operators.
AND was handed something that is not a condition - most often a bare cell reference to a text cell, as in AND(B2, C2>10). It can also happen when every argument is a range containing no logical values at all. Turn each argument into an explicit comparison and the error goes.