The IF function in Excel
IF makes a decision: it tests a condition, then returns one value when the condition is true and another when it's false. Pass or fail, bonus or no bonus, "Late" or "On time": that's IF.
Conditions use comparison operators: = (equal), <> (not equal), >, <, >= and <=. Combine several conditions with AND and OR.
Syntax
=IF(logical_test, value_if_true, [value_if_false])
| Argument | What it means |
|---|---|
| logical_test | A condition that is TRUE or FALSE, e.g. B2>=50. |
| value_if_true | What to return when the condition is true. Text goes in double quotes. |
| value_if_falseoptional | What to return when it's false. If left out, IF returns FALSE. |
Examples
1. Pass or fail
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Noa | 72 | Pass |
| 3 | Liam | 48 | |
| 4 | Maya | 91 |
72 is at least 50, so the condition is true and IF returns "Pass". Copied down, Liam (48) gets "Fail".
2. Calculate a number only when a condition is met
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Bonus |
| 2 | Dana | 12000 | 600 |
Sales above 10,000 earn a 5% bonus. 12,000 × 5% = 600; otherwise the bonus is 0.
3. More than two outcomes: nested IF
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Noa | 72 | |
| 3 | Liam | 48 | |
| 4 | Maya | 91 | A |
The second IF runs only when the first test is false. Order matters: check the highest band first. 91 → "A".
4. Two conditions at once with AND
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Attended | Certificate |
| 2 | Noa | 72 | Yes | Certified |
AND is TRUE only when both conditions are true. Use OR when either is enough.
Common mistakes
Text without quotes, e.g.
=IF(B2>=50,Pass,Fail)gives #NAME?.Fix: Put text in double quotes: "Pass".
Numbers in quotes, e.g. "100". The result is text and won't add up in SUM.
Fix: Type numbers without quotes:
=IF(B2>10000,100,0).Nested IFs in the wrong order. Testing B4>=75 before B4>=90 gives "B" to everyone above 75.
Fix: Test the strictest condition first, or use IFS in Excel 2019 and newer.
Frequently asked questions
How many IFs can I nest?
Excel allows up to 64, but more than 3 becomes hard to read. Use IFS, a lookup table with XLOOKUP, or VLOOKUP with an approximate match instead.
How do I check if a cell is empty?
Compare it with empty quotes: =IF(B2="","Missing","OK").
Is the text comparison in IF case-sensitive?
No, "yes" and "Yes" are equal. Use EXACT inside the test when case matters.
Related functions
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.