Excel IF function examples: text, AND, OR, nested IF and IFS

What does the IF function do in Excel?

IF lets a formula choose between two outcomes. You give it a test: if the test is true, the cell shows one thing, and if it isn't, the other. =IF(B2<12,"child","adult") shows child for anyone under 12 and adult for everyone else. Microsoft calls IF "one of the most popular functions in Excel" on its own help page (Microsoft).

Want to learn it step by step, with the explanation right on the sheet? Follow the lesson Choosing with IF. Below you'll find the syntax, an example for every kind of test, IF with AND and OR, more than two outcomes and the mistakes that come up most, all on the same sheet.

How do you write an IF statement in Excel?

An IF formula has three arguments, separated by commas: the test, what the cell shows when the test is TRUE, and what it shows when the test is FALSE. Only the last one is optional (Microsoft).

=IF(logical_test,value_if_true,[value_if_false])

The test is almost always a comparison: B2<12 asks whether B2 is less than 12, C2="Single" whether C2 says Single. A result can be text, a number, a reference or a calculation. Text goes in quotation marks (Microsoft), numbers don't. Type the comma but nothing after it, as in =IF(B2<12,"child",), and you get 0 instead of FALSE.

The comma between the arguments isn't fixed, by the way. Which separator Excel uses depends on your operating system's locale and your Excel settings, and the comma and the semicolon are the most common (Microsoft). That's why a colleague in Belgium or the Netherlands writes =IF(B2<12;"child";"adult"), or in Dutch Excel =ALS(B2<12;"kind";"volwassene").

How does IF work on a real sheet?

You type the formula once in the first row and fill it down. IF then decides again for every row, using that row's values. Take the members sheet of The Splash swimming pool in Tamworth, the third client in our Excel course. Children under 12 get the summer pass at half price, and Dave at the front desk wants to see for each member whether they're a child.

F2 holds =IF(B2<12,"child","adult"). Jack is 9, the test is TRUE, so F2 shows child. Fill the formula down to F11 and B2 moves along to B3, B4 and so on, so IF looks at the age on its own row each time. Only Jack and Rory are under 12. Rita and Jim are seniors, but they get adult too: this formula only knows two outcomes.

Now try it yourself, on the real sheet of The Splash. This time you test text rather than a number, and the result is an amount.

Your formula ran in your own browser, and then we checked it again on a second, hidden version of the sheet with other passes. Type the amounts in by hand and it works here but gets caught there.

Which comparisons can you use in an IF test?

Six: equal to, not equal to, less than, greater than, and the last two with "or equal to" added. Every comparison gives TRUE or FALSE (Microsoft), and that's exactly what IF needs as its first argument. Here's each operator on The Splash's sheet:

Watch the boundary. D2>15 and D2>=15 only differ for someone who came exactly 15 times, and that's Rita. If the rule says "from 15 visits", she's in, and you write >=. Not sure? Say the rule out loud for that one row. You can practise comparisons on their own in the lesson Comparing.

How do you test for text?

Text goes in quotation marks in the test, just like in the result. Case doesn't matter: =IF(C2="single",5,0) gives Jack 5 as well, even though his cell says Single with a capital S. If case has to count, put EXACT inside the test, a function Microsoft describes as case-sensitive (Microsoft). An extra space does count, though, as you'll see in the mistakes below.

Can the result of IF be a calculation?

Yes. A result can be a number, a reference or a whole calculation. Say children only pay half of the amount in column E:

=IF(B2<12,E2/2,E2)

That gives £7.50 for Jack and £12.50 for Rory, and everyone else keeps the amount from column E. Don't put quotation marks around the calculation: "E2/2" is text, and the cell would literally show E2/2 instead of an amount.

How do you leave a cell blank with IF?

With two quotation marks and nothing in between. According to Microsoft, "" essentially means "nothing" (Microsoft). The first formula below only shows something for members who came 10 times or more:

=IF(D2>=10,"frequent","")
=IF(D2="","unknown",D2)

Nora, Rita, Jim and Hannah get frequent, and the others a cell that looks empty. The other way round, "" tests whether a cell is empty. The second formula shows unknown for Laura, whose visits nobody filled in, and the number otherwise. Sara gets 0: her cell isn't empty, it holds a 0.

How do you use IF with AND and OR?

You put AND or OR inside the test of IF. AND returns TRUE when every condition holds, OR when at least one does, and IF then picks its result (Microsoft). On The Splash's second sheet, Dave keeps track of the swimming badges as TRUE or FALSE. You can only go in the deep end with badge A and badge B. Say one badge is enough for the club gala:

=IF(AND(C2,D2),"deep","shallow")
=IF(OR(C2,D2),"can enter","not yet")

With AND, four children may go in the deep end: Ilias, Ben, Theo and Sonny, who have both badges. With OR, six can enter the gala, because Ella and Amira have badge A, and that's enough. Since C and D already hold TRUE or FALSE, there's nothing left to compare. On the members sheet you just write the comparisons as arguments: AND(C2="Annual",D2>=20) is only true for Nora.

Two things often go wrong. The conditions go inside the function as arguments: IF(C2 AND D2,…) isn't something Excel understands. And pick the right one: =AND(A2="Ella",A2="Ben") is always FALSE, because one cell can't hold two names at once. If you mean "Ella and Ben", you mean OR. You'll practise NOT, which flips a test, along with these two in the lesson AND, OR and NOT.

How do you nest IF functions for more than two outcomes?

Put a second IF where the third argument goes. If the first test fails, Excel works out the second IF, and that gives you three outcomes. The summer pass gets three prices: one for children, one for seniors from 65 and one for everyone else. Both formulas below give every member their category:

=IF(B2<12,"child",IF(B2>=65,"senior","adult"))
=IFS(B2<12,"child",B2>=65,"senior",TRUE,"adult")

Jack and Rory become child, Rita and Jim senior, and the others adult, with one exception: Hannah becomes senior, although she's 12. That's down to the age stored as text, and the next section explains it.

IFS lists the tests one after another, each followed by its result, and Microsoft says it's "much easier to read" (Microsoft). If no test is true, it returns #N/A. That's why the last test is TRUE, which always holds: it's your default result. IFS works from Office 2019 on and with Microsoft 365. If someone opens your file in an older Excel, stick with the nested IF.

With a nested IF and with IFS, the first test that's true wins. So put the strictest test first.

You'll build both in the lesson More than two choices.

What's the difference between IF and IFERROR?

IF tests a condition you choose. IFERROR only checks whether a formula returns an error value, and shows something else if it does (Microsoft). Dave wants to know what each member pays per visit: the amount divided by the number of visits. For Sara, who hasn't been yet, and for Laura, whose visits are empty, that division returns #DIV/0!.

=IF(D2=0,"-",E2/D2)
=IFERROR(E2/D2,"-")

On this sheet they give the same: £5 per visit for Jack, £16 for Rita, and a dash for Laura and Sara. The difference is in what they catch. IF catches exactly the case you test. IFERROR catches every error value, including the #NAME? from a typo in your own formula, which you then no longer see. So only wrap it around a formula that works, as you'll practise in the lesson Catching error values.

Why isn't my IF formula working?

Usually because of one of these six things. You'll spot the first one straight away by its error value. The others return a result that looks believable, and those are the dangerous ones.

Text without quotation marks gives #NAME?

=IF(B2<12,child,adult) shows #NAME? on every row. Without quotation marks, Excel reads child as a name, looks for it and can't find it (Microsoft). Put every piece of text in quotation marks. A typo in the function name gives the same error.

A number stored as text

Hannah's age was typed as text, and you'll often spot a cell like that by its left alignment and a small green triangle. =IF(B10>=65,"senior","regular") returns senior for her: Excel doesn't compare the text "12" as the number 12. With B10<12 she got adult, but that was right by accident. Numbers stored as text can cause unexpected results, and you fix them with Convert to Number or the VALUE function (Microsoft).

One space too many

If C2 says "Single " with a trailing space, C2="Single" returns FALSE: to Excel that's different text. You can't see the space, and it easily slips in with data you copy from somewhere else. TRIM removes all spaces except single spaces between words (Microsoft), so with =IF(TRIM(C2)="Single",5,0) the test works again.

The order of nested IFs

Excel runs through the tests and stops at the first one that's true. In =IF(B2>=12,"adult",IF(B2>=65,"senior","child")), Rita and Jim become adult: 67 and 72 are 12 or more too, so the second test never gets a turn. In Microsoft's own example, revenue of $12,500 gets the lowest commission this way (Microsoft). Put the strictest test first.

Semicolons in a formula from a European colleague

Copy =ALS(B2<12;"kind";"volwassene") from a Dutch forum and you have two things to change in English Excel: ALS becomes IF, and the semicolons become commas. Which separator your Excel expects depends on the locale (Microsoft), not on the language. With English Excel and Belgian settings, it's =IF(B2<12;"child";"adult"): English names, but semicolons.

Too many nested IFs

Excel lets you nest up to 64 IF functions, but Microsoft itself says it's "not at all advisable" (Microsoft): a formula like that is hard to build correctly and hard to maintain. With many outcomes, choose IFS, or put the thresholds in a table and look them up with VLOOKUP, as Microsoft suggests.

Which functions are related to IF?

IF decides between two outcomes, cell by cell. For anything beyond that, Excel has functions from the same family. If you work with colleagues in Belgium or the Netherlands, the Dutch names are handy too:

SWITCH fits when you compare one cell with a fixed list. =SWITCH(C2,"Single",5,"Monthly",2,0) gives 5 for a single swim, 2 for a monthly pass and 0 for the rest. The last argument is the default; without it, SWITCH returns #N/A when nothing matches (Microsoft). Like IFS, it works from Office 2019 on and with Microsoft 365.

If you want one total instead of a result per member, you don't need IF at all. =COUNTIF(B2:B11,"<12") counts the children: 2. =SUMIF(C2:C11,"Annual",E2:E11) adds up what the annual pass holders paid: £960. You'll find more examples in COUNTIF explained and SUMIF with examples.

The formulas you'll need most are in 12 basic Excel formulas everyone should know, and every function with its Dutch name is on the Excel cheat sheet. IF sticks best when you type it yourself: in module 3 of our Excel course for beginners you do that at The Splash, with exercises we check on their result.