Function guides ยท Logical
IF function
Returns one value if a condition is true and another if it is false.
Syntax
=IF(logical_test, value_if_true, [value_if_false])
| Argument | What it is |
|---|---|
logical_test | A comparison that is TRUE or FALSE, such as B2>=70. |
value_if_true | What to return when the test is TRUE. |
value_if_false | Optional. What to return when it is FALSE. Omitted, it returns FALSE. |
What IF does
IF makes a decision. You give it a test, a result for when the test holds, and a result for when it does not.
The test is any expression that comes out TRUE or FALSE: a comparison like B2>=70, a function such as ISBLANK(B2), or a combination built with AND and OR. Comparison operators are =, <>, >, <, >= and <=.
The results can be text, numbers, cell references, calculations or another IF. That last option is what people mean by a nested IF.
IF examples
1. Pass or fail from a score
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Result |
| 2 | Ana | 92 | Pass |
| 3 | Dev | 64 | Fail |
The test asks a yes-or-no question about the score in the same row. Excel returns the second argument when the answer is yes and the third when it is no. Ana's row (C2) passes with 92. The highlighted formula in C3 tests B3 and Dev's 64 fails the cutoff.
Text results go in double quotes. Numbers and cell references do not.
2. Calculate a bonus
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Bonus |
| 2 | Mei | 12500 | 625 |
| 3 | Omar | 8300 |
The result does not have to be text. This pays a 5 percent bonus on sales over 10,000 and nothing otherwise. The true branch is a calculation, the false branch a plain zero.
Putting the 10,000 and 5% into their own cells and pointing at them is better practice, because a rule change then means editing one cell.
3. Nested IF for three outcomes
| A | B | C | |
|---|---|---|---|
| 1 | Name | Hours | Status |
| 2 | Kai | 42 | Overtime |
A second IF can sit in the false branch. Excel checks the first test; if it fails, it moves to the next. The order matters because the first true test wins.
With three outcomes this is readable. Beyond four or five levels, switch to IFS or a lookup table.
Common IF mistakes
- Forgetting quotes around text.
=IF(B2>=70,Pass,Fail)returns #NAME?, because Excel thinks Pass is a name. Put text results in double quotes. - Quoting numbers.
=IF(B2>10000,"625",0)returns text. It looks right but will not add up. Leave numbers unquoted. - Wrong order in a nested IF.
=IF(B2>=70,"Pass",IF(B2>=90,"Honors","Fail"))never returns Honors, since 95 already passed the first test. Put the strictest condition first. - Comparing text with different case or spaces. Excel's = is not case sensitive, but a trailing space makes "West " different from "West". Wrap the cell in TRIM.
IF on Excel job tests
Most IF tasks come as a column of numbers and a rule in words: mark orders over $500 as Large, otherwise Small. Turn the rule into a comparison, quote the labels, and fill down.
Watch the boundary words. "Over 500" is greater than, "500 or more" is greater than or equal, and data sets tend to include a row of exactly 500. Dates behave the same way: "on or before March 31" needs <= and a DATE or a cell reference.
If the sheet already holds the cutoff in a cell, point at it instead of typing the number into the formula. Then changing the rule means changing one cell, and the formula survives the change.
Related functions
IF questions
- How many IFs can I nest?
- Excel allows 64 levels, but few people can read more than three. Past that, use IFS or a VLOOKUP against a small table.
- How do I test two conditions in IF?
- Wrap them in AND or OR:
=IF(AND(B2>=70,C2>=70),"Pass","Fail"). AND needs every condition true, OR needs any one. - How do I leave the cell blank when the test is false?
- Return two double quotes:
=IF(B2>10000,B2*0.05,""). The cell looks empty, though it holds an empty string and ISBLANK will say FALSE. - What is the difference between IF and IFS?
- IF handles one test and a fallback. IFS lists several tests in order with no nesting, but it returns #N/A if none of them is true unless the last test is TRUE.
Practice IF
Try it in a working grid: the free lesson IF: choose between two results (module 7 of Everyday Excel) has checked IF exercises.
Practice this function