IF in Google Sheets

Returns one value when a test is TRUE and another when it is FALSE. Put text results in double quotes.

In Google Sheets

IF works the same way in Google Sheets. Over a whole range, wrap it in ARRAYFORMULA to fill a column with one formula.

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

Related functions