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

C1=IF(A2>=50,"Pass","Fail")
ABC
1ScorePass
262

In plain words: If the score in A2 is 50 or more, show Pass. Otherwise show Fail.

C1=IF(A2>100,A2*0.1,0)
ABC
1Score0
262
C1=IF(ISNUMBER(SEARCH("pro",A2)),"Pro","Standard")
ABC
1ProductPro
2Laptop PRO 14

In plain words: If the product name in A2 contains pro, show Pro. Otherwise 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+D2 cannot 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.

Related functions