The IF function
Returns one value when a test is TRUE and another when it is FALSE. Put text results in double quotes.
How to use IF
IF is the yes-or-no question of spreadsheets. You give it a test, and it answers with one thing when the test is TRUE and another when it is FALSE. Think of a bouncer with a guest list: if you are on the list, you go in, otherwise you don't.
Reach for IF whenever one cell should depend on another. Mark invoices as paid or open, show a bonus only above a sales target, or flag staff who have not handed in a timesheet. The test is usually a comparison such as A2>=50, B2="Paid" or C2<>"". Text results go in double quotes, numbers do not: =IF(B2>10000,B2*0.05,0).
When one test is not enough, put AND or OR inside it: =IF(AND(B2>=50,C2="Yes"),"Pass","Fail"). For three or more outcomes you can nest IFs, but IFS is easier to read. If the outcomes sit in a list you may change later, a lookup table with VLOOKUP or XLOOKUP is better still. If you leave out the third argument, IF returns FALSE when the test fails.
Syntax
IF(logical_test, [value_if_true], [value_if_false])- logical_test
- A comparison that is TRUE or FALSE, such as A2>=50.
- value_if_true (optional)
- What to return when the test is TRUE.
- value_if_false (optional)
- What to return when the test is FALSE.
Examples
=IF(1A2>=50,2"Pass",3"Fail")| A | B | C | |
|---|---|---|---|
| 1 | Score | Pass | |
| 2 | 62 |
In plain words: 1If the score in A2 is 50 or more, 2show Pass. 3Otherwise show Fail.
| A | B | C | |
|---|---|---|---|
| 1 | Score | 0 | |
| 2 | 62 |
=IF(1ISNUMBER(SEARCH("pro",A2)),2"Pro",3"Standard")| A | B | C | |
|---|---|---|---|
| 1 | Product | Pro | |
| 2 | Laptop PRO 14 |
In plain words: 1If the product name in A2 contains pro, 2show Pro. 3Otherwise show Standard.
Common IF mistakes
IF shows #NAME? instead of my text
Text in the test and in the results needs double quotes, as in
=IF(A2="Paid","Done","Open"). Without quotes, the program reads Paid as a name it does not know.The test never matches a number I can clearly see
Either the number sits in quotes in the formula (
"50"is text) or the cell holds a number stored as text, which is usually left-aligned. Write=IF(A2=50,"Yes","No")without quotes, or convert the cell first with=IF(VALUE(A2)>=50,"Pass","Fail").Every row gets the same result from my nested IF
IF checks the tests from the outside in and stops at the first TRUE. Start with the strictest one:
=IF(A2>=90,"High",IF(A2>=60,"Mid","Low")). If you test A2>=60 first, every score of 90 or more also ends up as Mid.I get #VALUE! when I add up cells that IF left "empty"
The result
""looks empty but is text, and=C2+D2cannot add text. Use=SUM(C2:D2), which skips text, or return 0 instead of""and hide the zeros with a number format.
Practice IF
Read the guide
Questions
How do I check if a cell contains text?
IF doesn't understand wildcards, so combine it with SEARCH and ISNUMBER, =IF(ISNUMBER(SEARCH("pro",A2)),"Pro","Standard"). SEARCH ignores case, and FIND tells capitals apart. To check whether a cell holds any text at all, use =IF(ISTEXT(A2),"Text","Not text").
How do I use IF with multiple conditions?
Put AND or OR in the test. =IF(AND(B2>=50,C2="Yes"),"Pass","Fail") needs both to be true, and =IF(OR(B2>=90,C2="Yes"),"Bonus","") needs one of them. For more than two outcomes, nest IFs or use IFS.
How do I check if a cell is empty with IF?
Compare the cell with two double quotes, =IF(A2="","Missing","OK"), or use ISBLANK, =IF(ISBLANK(A2),"Missing","OK"). A2="" is also TRUE when A2 holds empty text from another formula, while ISBLANK is TRUE only for a truly empty cell.
How do I leave the cell blank when the condition is not met?
Use two double quotes as the result, as in =IF(B2>10000,B2*0.05,""). The cell looks empty, but it holds empty text, so ISBLANK returns FALSE for it.
How many IFs can I nest?
Excel allows 64, but past three or four the formula is hard to read. Use IFS, or a lookup table with VLOOKUP or XLOOKUP, instead.