IF with AND — Test Multiple Conditions in Excel and Google Sheets
Purpose
Combining IF with AND is one of the most powerful and frequently used formula patterns in Excel and Google Sheets.…
Combining IF with AND is one of the most powerful and frequently used formula patterns in Excel and Google Sheets. IF on its own handles one condition. AND on its own returns TRUE or FALSE. Together they let you test multiple conditions simultaneously and return completely different outputs based on whether all conditions pass. This is the pattern behind virtually every approval workflow, eligibility check, and multi-criteria flag in business spreadsheets. Is an invoice within budget AND from an approved vendor AND within terms? Flag it for auto-payment. Does an employee have enough tenure AND a passing performance score AND no active disciplinary actions? Mark them eligible for promotion. IF AND handles all of these cleanly in a single readable formula.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=IF(AND(condition1, condition2, ...), value_if_true, value_if_false)
Arguments
| Argument | Required | Description |
|---|---|---|
| AND(condition1, condition2, ...) | Required | The logical test for IF. AND evaluates all conditions and returns TRUE only when every condition is met. This TRUE or FALSE drives which branch of IF executes. You can include up to 255 conditions inside AND. |
| value_if_true | Required | What IF returns when AND evaluates to TRUE — meaning all conditions inside AND are met simultaneously. Can be text in quotes, a number, a blank with empty quotes, or another formula. |
| value_if_false | Optional | What IF returns when AND evaluates to FALSE — meaning at least one condition inside AND failed. If omitted, IF returns FALSE. Use empty quotes to return a blank cell. |
How it works
IF with AND works in two stages. First AND evaluates all the conditions you provide and produces a single TRUE or FALSE result. If every condition is TRUE AND returns TRUE. If even one condition is FALSE AND returns FALSE immediately without checking the rest. Second, IF receives that TRUE or FALSE from AND and returns either value_if_true or value_if_false accordingly. The key mental model is that AND collapses multiple conditions into a single logical answer, which IF then acts on. This is much cleaner than the alternative of nesting multiple IFs: =IF(A1>0,IF(B1>0,IF(C1>0,"All positive","C failed"),"B failed"),"A failed"). The nested version is harder to read and returns different messages for different failures. IF AND simplifies this: =IF(AND(A1>0,B1>0,C1>0),"All positive","At least one is not positive"). If you need to know which specific condition failed, nested IFs or the IFS function are better choices. But when you just need to know whether all conditions pass and want the same outcome for any failure, IF AND is the cleanest solution.
Examples
1
Flag employees who are eligible for promotion based on three criteria: minimum two years service, performance rating above 3.5, and zero disciplinary actions.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Years | Rating | Incidents | Eligible? |
| 2 | Alice | 3 | 4.2 | 0 | =IF(AND(B2>=2,C2>3.5,D2=0),"Eligible","Not Eligible") |
| 3 | Bob | 1 | 4.8 | 0 | Not Eligible |
| 4 | Carol | 4 | 3.2 | 0 | Not Eligible |
| 5 | David | 2 | 4.1 | 1 | Not Eligible |
| 6 | Eve | 5 | 4.7 | 0 | Eligible |
| 7 | Frank | 3 | 3.8 | 0 | Eligible |
| 8 | Grace | 1 | 4.5 | 0 | Not Eligible |
| 9 | Hank | 6 | 4.0 | 0 | Eligible |
| 10 | Iris | 2 | 4.3 | 2 | Not Eligible |
| 11 | James | 4 | 3.9 | 0 | Eligible |
Row 2: Eligible
— Alice has 3 years (≥2), a 4.2 rating (>3.5), and zero incidents — all three conditions pass.
Bob fails on years of service despite an excellent rating. Carol fails on rating despite meeting the tenure requirement. David fails on incidents — even one disciplinary action disqualifies. Grace has the highest rating but two incidents disqualify her. Frank has exactly 2 years which qualifies because we used >= not >. All three AND conditions must be simultaneously true for Eligible to show.
2
Auto-approve invoices that are under 10,000, from an approved vendor, and within 30 payment days.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Amount | Vendor Status | Pay Days | Decision |
| 2 | INV-001 | 7500 | Approved | 28 | =IF(AND(B2<10000,C2="Approved",D2<=30),"Auto-Approve","Review") |
| 3 | INV-002 | 12000 | Approved | 20 | Review |
| 4 | INV-003 | 4200 | Pending | 15 | Review |
| 5 | INV-004 | 8900 | Approved | 45 | Review |
| 6 | INV-005 | 3100 | Approved | 25 | Auto-Approve |
| 7 | INV-006 | 9800 | Approved | 30 | Auto-Approve |
| 8 | INV-007 | 5500 | Blacklisted | 10 | Review |
| 9 | INV-008 | 6700 | Approved | 28 | Auto-Approve |
| 10 | INV-009 | 11000 | Approved | 22 | Review |
| 11 | INV-010 | 2400 | Approved | 35 | Review |
Row 2: Auto-Approve
— INV-001 is 7500 (<10000), vendor is Approved, and pay days is 28 (<=30). All three conditions pass.
INV-002 fails on amount (12000 >= 10000). INV-003 fails on vendor status. INV-004 fails on payment days (45 > 30).
Common use cases
1. Auto-approve invoices that are under budget, from an approved vendor, and within payment terms
=IF(AND(B2<10000,C2="Approved",D2<=30),"Auto-Approve","Review")
2. Flag loan applications where credit score, income, and employment status all meet minimum requirements
=IF(AND(C2>=700,D2>=35000,E2="Employed"),"Approved","Declined")
3. Mark products as ready to ship when in stock, quality-passed, and labelled correctly
=IF(AND(B2>0,C2="Passed",D2="Complete"),"Ready","Hold")
4. Identify students eligible for a scholarship based on GPA, attendance, and financial need status
=IF(AND(B2>=3.5,C2>=90,D2="Yes"),"Scholarship Eligible","Not Eligible")
5. Apply a discount only to orders above minimum value, from loyalty customers, in a qualifying category
=IF(AND(B2>=500,C2="Loyalty",D2="Premium"),B2*0.9,B2)
Common errors
Returns Not Eligible or False unexpectedly
One of the conditions inside AND is failing silently. Common causes are extra spaces in text values where "Sales " does not equal "Sales", data type mismatch where a number is stored as text, or a wrong comparison operator like > where >= is needed for boundary values.
Fix: Test each AND condition separately in an empty cell to identify which one is returning FALSE. Use TRIM to remove spaces. Use VALUE or TEXT to align data types. Check whether boundary values should use >= or > depending on your logic.
#VALUE!
One of the conditions inside AND is referencing a cell with an error value, or a comparison is being made between incompatible types like text and a date serial number.
Fix: Use IFERROR around the whole formula or check the individual cells being referenced for existing errors. Wrap date comparisons using DATEVALUE or DATE function to ensure consistent comparison types.
Formula always returns the same result
All the condition ranges or values reference the same cell, all conditions are equivalent, or there is a logic error in the conditions that makes them always true or always false.
Fix: Check that each condition references different cells or columns. Verify the comparison direction — a condition like B2>B2 is always false while B2>=0 might always be true. Test with clearly passing and clearly failing values.
Tips and variations
Use IF AND OR together for complex mixed logic
When some conditions must all be true AND others need just one to be true, combine AND and OR in the same formula. Nest OR inside AND or vice versa depending on your logic. This lets you express sophisticated business rules in a single readable formula rather than multiple helper columns or complex nested IFs.
=IF(AND(B2>1000,OR(C2="Gold",C2="Platinum")),"Priority","Standard")
Return a blank instead of a message when conditions are not met
Using empty quotes as value_if_false returns a visually blank cell rather than showing Not Eligible, No, or FALSE. This keeps your spreadsheet clean when the negative result does not need a label. Cells with blank results are also easier to filter and count separately from cells with positive results.
=IF(AND(B2>=2,C2>3.5,D2=0),"Eligible","")
Use IF AND in conditional formatting to highlight entire rows
In conditional formatting apply a custom formula rule using IF AND — or just AND directly since conditional formatting already expects TRUE or FALSE. This highlights entire rows when all your specified conditions are met, making visual identification of qualifying records instant without needing a helper column.
=AND($B2>=2,$C2>3.5,$D2=0)
Excel vs Google Sheets
Excel vs Google Sheets
IF combined with AND works identically in Microsoft Excel and Google Sheets. The syntax, the AND logic, the number of conditions supported (up to 255), and the IF behaviour are all the same in both applications. Formulas copy between Excel and Google Sheets without any changes. In Google Sheets, IF AND also works inside ARRAYFORMULA to apply the multi-condition check across entire column ranges simultaneously without copying the formula down, which is useful for large datasets.
Related reading
Frequently asked questions
IF AND is cleaner and more readable when all conditions lead to the same outcome. =IF(AND(A1>0,B1>0,C1>0),"All positive","At least one failed") is much easier to read than three nested IFs. Use nested IF when different conditions should produce different outputs — for example when you want to report specifically which condition failed. For more than three or four outcome levels consider the IFS function which is cleaner than deep nesting.
AND supports up to 255 conditions in both Excel and Google Sheets. In practice I recommend keeping it to five or fewer for readability. If you find yourself with more than five conditions it is often a sign that your data structure could be improved, or that some conditions could be combined. Formulas with many conditions are hard to debug when something goes wrong.
Yes. value_if_true and value_if_false can be any value: text in quotes, a number without quotes, an empty string, or another formula. =IF(AND(A1>100,B1="Active"),A1*0.1,0) returns 10% of the value if both conditions pass or zero if not. You can also use other functions: =IF(AND(A1>0,B1>0),SUM(A1:B1),0) sums two cells only when both are positive.
Yes, IF AND is completely identical in both applications. Same syntax, same AND logic, same 255-condition limit, same IF behaviour. Formulas copy between Excel and Google Sheets without any changes. Google Sheets additionally supports IF AND inside ARRAYFORMULA for array-based processing across entire columns in a single formula, which Excel 365 matches with its dynamic array engine.
The easiest approach is to temporarily evaluate each condition separately in empty cells. Put =B2>=2 in one cell, =C2>3.5 in another, =D2=0 in a third. Whichever returns FALSE is the failing condition. You can also use the Evaluate Formula tool in Excel under the Formulas tab to step through the formula evaluation and see exactly which AND argument returns FALSE. In Google Sheets, break the formula apart across multiple columns for the same diagnostic effect.