Combining conditions: AND, OR and nested IF
9 minutes · 4 steps · free, no sign-up
A single IF can only ask one question. By combining conditions with AND and OR, and putting one IF inside another, a single formula can make much richer decisions.
- Require two things at once with AND
- Accept either of two things with OR
- Put an IF inside an IF to give three or more answers
- Fill formulas down a column
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceCombining tests
=AND(test1, test2) true only if BOTH are true
=OR(test1, test2) true if EITHER is true
=IF(AND(B2>=50, C2>=80), "Pass", "Fail")
=IF(B2>=80, "A", IF(B2>=60, "B", "C"))The key ideasSummary: Combining conditions: AND, OR and nested IF
AND and OR turn several tests into one true-or-false answer, which you can drop straight into an IF. Use AND when everything must be true and OR when any one will do.
When you need more than two outcomes, put an IF in the "false" slot of another IF. Sheets checks the first test; if it fails it moves on to the next. Order matters: test the strictest condition first, as in the grade example where 80 or more is checked before 60 or more.
Watch out forCommon mistakes
- Testing in the wrong order. Sheets uses the first test that is true. If you check
B2>=60beforeB2>=80, every high score is graded B. Put the strictest condition first. - Losing track of brackets. Each
IF(needs its own closing bracket, and nested formulas end with several. Sheets colour-codes matching pairs while you edit. - No answer for the leftover case. If you leave out the final "else" value, anyone who matches nothing gets
FALSEinstead of a label.
Next stepTry it in the real Google Sheets
Real Google Sheets has a tidier alternative to deep nesting: IFS. The grade formula becomes =IFS(B2>=80,"A",B2>=60,"B",TRUE,"C"), where the final TRUE acts as the catch-all. The SWITCH function is similar for exact matches. The practice sheet only supports plain IF, which is worth knowing because older spreadsheets are full of nested IFs.