IF checks one condition and returns one value if it’s true and another if it’s false. Real decisions usually need more: pass only if the score and the attendance are high enough, or give a grade from several bands. There are four ways to do that in Google Sheets: AND and OR inside IF, nested IFs, IFS, and SWITCH. We ran each one on the same ten students. Two of the mistakes below gave wrong grades with no error.
The data, in A1:D11:
| A: Name | B: Score | C: Attendance | D: Status | |
|---|---|---|---|---|
| 2 | Ava | 92 | 95% | Submitted |
| 3 | Leo | 78 | 80% | Submitted |
| 4 | Mia | 65 | 70% | Missing |
| 5 | Noah | 85 | 60% | Submitted |
| 6 | Zoe | 50 | 90% | Missing |
| 7 | Eli | (empty) | 85% | Pending |
| 8 | Ivy | 90, typed as text | 100% | Submitted |
| 9 | Max | 100 | 75% | Submitted |
| 10 | Sam | 0 | 0% | Missing |
| 11 | Uma | 89.5 | 92% | Submitted |
Each formula below was written in row 2 and copied down to row 11.
Tested in Google Sheets. Every formula on this page was run on November 13, 2026, and the results below are the ones Google Sheets returned. How we test
AND: every condition must be true
=IF(AND(B2>=80, C2>=0.8), "Pass", "Fail")
Pass needs a score of at least 80 and attendance of at least 80%. Ava, Ivy and Uma passed. Noah scored 85 but attended 60%, and Max scored 100 with 75%, so both failed. AND takes as many conditions as you need; adding D2="Submitted" as a third gave the same three passes.
Multiplying the conditions does the same job: =IF((B2>=80)*(C2>=0.8), "Pass", "Fail") returned identical results. That form matters when you fill a whole column with one formula, as explained in ARRAYFORMULA with IF.
OR: at least one condition is true
=IF(OR(B2>=90, C2>=0.95), "Merit", "")
Merit for a score of 90 or more, or attendance of 95% or more. Ava, Ivy and Max got it. The empty quotes as the last argument leave the other cells blank.
Several bands: nested IF
To turn a score into a grade, put one IF inside another. Each IF handles one band and passes the rest to the next:
=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "F")))
The grades were A, C, F, B, F, F, A, A, F, B. Uma’s 89.5 got a B, since it’s below 90.
The order of the tests matters
Found in testing: with the same three tests in the opposite order, =IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))), every student scoring 70 or more got a C, Max’s 100 included. IF stops at the first true test, and “at least 70” is true for every higher score too. There was no error. Test the highest band first when you use “>=”, or the lowest first when you use “<“.
IFS: the same grades without nesting
IFS takes pairs of a condition and a result, and returns the result of the first true condition:
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", TRUE, "F")
This returned the same grades as the nested IF. It’s easier to read, but it has two catches:
- No “otherwise” by default. Without the last pair,
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C")returned #N/A with the message No match. for Mia, Zoe, Eli and Sam. The finalTRUE, "F"is a condition that is always true, so it catches everything left. - The order matters in the same way as with nested IF: the first true condition wins.
SWITCH: one cell, several exact values
When you compare a single cell with a list of exact values, SWITCH is shorter than IFS:
=SWITCH(D2, "Submitted", "Done", "Missing", "Chase", "?")
It returned Done, Chase, or the last argument, “?”, for anything else: Eli’s “Pending”. SWITCH only checks for equal values, so it can’t do “greater than 80”.
Two mistakes that change the result silently
A number typed as text
Ivy’s score is the text “90”, not the number 90. In Google Sheets, text compares as greater than any number, so B8>=80 was TRUE and Ivy got Pass, A and Merit everywhere. Here the result happens to be right, but it isn’t the number that decides: =IF("10">=80, "Pass", "Fail") also returned Pass, and ="10">=1000000 returned TRUE. =ISTEXT(B8) returned TRUE, which is how to find such cells. Our guide to converting text to numbers shows how to fix them.
Putting the number itself in quotes has the same effect the other way round: =IF(B2>="80", "Pass", "Fail") returned Fail for every real number, 100 included, and Pass only for Ivy’s text “90”.
An empty cell counts as 0
Eli has no score, and every formula treated the empty cell as 0: Fail and F. If an empty cell means “not graded yet”, test for it first:
=IF(B2="", "No score", IF(B2>=80, "Pass", "Fail"))
This returned “No score” for Eli and the normal result for everyone else.
Leaving out the “false” value
| Formula | When the test is false |
|---|---|
=IF(B2>=80, "Pass") |
FALSE |
=IF(B2>=80, "Pass", ), with the comma |
An empty cell |
=IF(B2>=80, "Pass", "") |
An empty-looking cell holding “” |
Use "" when you want a blank result. Keep in mind that some functions count cells holding "" as filled: see COUNTIF not blank.
Which one to use
- Two outcomes, several conditions: IF with AND or OR.
- Several bands of a number: IFS with a final
TRUE, or nested IF. For many bands, a lookup table with XLOOKUP’s approximate match is easier to maintain. - One cell against exact values: SWITCH.
- Counting rows that meet conditions, rather than labeling each row: COUNTIFS.