On this page ⌄
IF is how a spreadsheet makes decisions. It asks a yes-or-no question about your data, and gives back one answer for yes and another for no. Once you know IF, you can label rows, flag problems, skip bad data and build simple rules into any sheet.
The examples use this grade book in A1:E9:
| A: Student | B: Math | C: English | D: Science | E: Attendance | |
|---|---|---|---|---|---|
| 2 | Emma | 82 | 91 | 77 | 96% |
| 3 | Liam | 45 | 62 | 58 | 81% |
| 4 | Olivia | 93 | 88 | 95 | 99% |
| 5 | Noah | 67 | 49 | 72 | 88% |
| 6 | Ava | 58 | 74 | 61 | 92% |
| 7 | Ethan | 39 | 55 | 47 | 70% |
| 8 | Mia | 76 | 81 | 69 | 94% |
| 9 | Lucas | 88 | 59 | 90 | 85% |
How IF works
IF has three parts:
| Argument | What it means | Example |
|---|---|---|
| logical_test | A question that's either TRUE or FALSE | C3>=60 |
| value_if_true | What to return when the answer is TRUE | "Pass" |
| value_if_false | What to return when the answer is FALSE | "Fail" |
Did Liam pass English, if the pass mark is 60?
=IF(C3>=60, "Pass", "Fail")→ PassLiam scored 62, so C3>=60 is TRUE and IF returns "Pass". Copy the formula down and every student gets a label.
The question is almost always a comparison. These are the operators you can use:
| Operator | Meaning | Example |
|---|---|---|
= | equal to | A2="Emma" |
<> | not equal to | A2<>"Emma" |
> | greater than | B2>50 |
< | less than | B2<50 |
>= | greater than or equal to | B2>=50 |
<= | less than or equal to | B2<=50 |
Try it in a live sheet. Click Emma's Math score to put it in your formula.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
Did Emma pass Math? The pass mark is 50. Return "Pass" or "Fail".
Write your formula in the green cell, H2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
IF can return numbers and calculations too
The results don't have to be words. They can be numbers, cell references or whole calculations. Say students with attendance under 90% lose 5 points from their Math score:
=IF(E5<90%, B5-5, B5)→ 62Noah's attendance is 88%, so the test is TRUE and IF returns his score minus 5.
If you leave out value_if_false, IF returns the word FALSE when the test fails. To show nothing instead, use empty quotes:
=IF(E2>=95%, "Perfect attendance", "")IF with AND: every condition must be true
AND returns TRUE only when all of its conditions are true. Put it inside IF to check several things at once. Who made the honor roll, with at least 80 in both Math and English?
=IF(AND(B2>=80, C2>=80), "Honor roll", "")→ Honor rollEmma has 82 and 91, so both conditions pass.
IF with OR: at least one must be true
OR returns TRUE when any of its conditions is true. Flag any student who scored under 50 in Math or Science:
=IF(OR(B7<50, D7<50), "Check in", "OK")→ Check inEthan has 39 in Math, so the first condition is enough. It doesn't matter that he'd fail Science too.
Nested IFs: more than two outcomes
One IF gives you two outcomes. For more, put an IF inside the value_if_false of another IF. Here's a letter grade for English:
=IF(C2>=85, "A", IF(C2>=70, "B", IF(C2>=50, "C", "F")))→ AExcel checks each test in order and stops at the first TRUE one. That's why the order matters: start with the highest band. If you checked C2>=50 first, every passing student would get a C.
IFS: the cleaner way to write nested IFs
In Excel 2019 and later, IFS does the same job without the pile of brackets. You list pairs of test and result, and Excel returns the result for the first test that's TRUE:
=IFS(C2>=85, "A", C2>=70, "B", C2>=50, "C", TRUE, "F")→ AThe final TRUE, "F" is a catch-all. Without it, a student who matches none of the tests would get an #N/A error.
Common IF mistakes
| Problem | What's going on | Fix |
|---|---|---|
#NAME? | Text without quotes, like =IF(B2>=50, Pass, Fail) | Put quotes around text: "Pass" |
| Everything gets the same answer | Nested tests are in the wrong order | Check the biggest band first |
| A comparison never matches | The number is in quotes, like B2>="50", so Excel compares text | Remove the quotes around numbers |
| The cell shows FALSE | value_if_false was left out | Add a third argument, even if it's "" |
| Too many brackets to follow | Deeply nested IFs | Use IFS, or a lookup table with XLOOKUP |
When to use a lookup instead of IF
If you're nesting more than three or four IFs to map values to results, like region codes to region names, a small lookup table is easier to read and change. Put the pairs in two columns and use XLOOKUP to fetch the result.
Key takeaways
- IF needs a test that's TRUE or FALSE, a result for TRUE and a result for FALSE.
- Wrap text results in double quotes.
- Use AND when every condition must be true, and OR when any one is enough.
- For several bands, use nested IFs from highest to lowest, or IFS.
Practice
Keep going with IF and AND exercises
Reading gets you started. Solving a few of these is what makes it stick.
- 1Pass or failFreeDid Emma pass Math? The pass mark is 50. Return "Pass" or "Fail".→
- 2Needs a retakeFreeIf Liam scored below 50 in Math, return "Retake". Otherwise return "OK".→
- 3Strong in every subject PremiumDid Olivia score 85 or more in all three subjects? Return TRUE or FALSE.→
- 4Flag students for support PremiumReturn "Support" for Noah if any score is below 50 or attendance is under 90%. Otherwise return "On track".→
- 5Letter grades PremiumGive Mia a letter grade for Math: A for 85+, B for 70+, C for 50+, otherwise F.→
About the author
Brian Millot
Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.