The Excel IF function, with practice exercises

In short: =IF(logical_test,value_if_true,value_if_false). When the test is true Excel returns the second argument, otherwise the third. Text goes in double quotes, numbers and cell references do not.

Try it now

The sheet lists revenue and target for five branches. The status column should say whether each branch hit its target. Click the yellow cell and write the formula for the first branch.

Task In D2, write a formula that shows "Target met" if the revenue in B2 is at least the target in C2, and "Target missed" otherwise.

	A	B	C	D
1	Branch	Revenue $	Target $	Status
2	Berlin	48200	45000
3	Hamburg	39800	42000
4	Munich	51500	50000
5	Cologne	36900	40000
6	Leipzig	30100	30000

The IF formula

Argument	Meaning	In the example
logical_test	A comparison that is either true or false	B2>=C2
value_if_true	What to return when the test is true: text, a number, a reference or a calculation	"Target met"
value_if_false	What to return when the test is false. Leave it out and Excel shows FALSE.	"Target missed"

Put together: =IF(B2>=C2,"Target met","Target missed"). Excel compares 48,200 with 45,000, the test is true, so the first text appears. Fill the formula down and each row checks its own numbers, because B2 and C2 are relative references.

Comparison operators

Operator	Meaning	Example
=	equal to	A2="Berlin"
<>	not equal to	A2<>"Berlin"
>	greater than	B2>10000
<	less than	B2<C2
>=	greater than or equal to ("at least")	B2>=C2
<=	less than or equal to ("at most")	B2<=500

Text comparisons ignore case, so A2="berlin" is also true for "Berlin". To test for an empty cell, compare with two quotes and nothing between them: A2="".

One more: calculate a bonus

This time IF returns a number instead of text. Anyone with sales above 10,000 earns 5% of their sales as a bonus, everyone else gets 0.

Task In C2, calculate the bonus: if the sales in B2 are above 10,000, the bonus is 5% of sales, otherwise 0.

	A	B	C
1	Employee	Sales $	Bonus $
2	Emma Clark	12400
3	Liam Turner	8700
4	Olivia Hughes	15300
5	Noah Bennett	10000
6	Grace Porter	11250

The answer =IF(B2>10000,B2*5%,0) shows that value_if_true can be a whole calculation. Rather than hard-coding the rate, you can put it in its own cell and reference it absolutely, such as B2*$F$1, so a change later happens in one place.

AND and OR inside IF

When several conditions must all be true, wrap them in AND. When any one of them is enough, use OR. Either one takes the place of the logical test:

• =IF(AND(B2>=C2,D2="yes"),"Bonus","No bonus") pays a bonus only if the target is met and D2 says yes.
• =IF(OR(A2="Boston",A2="Chicago"),"North","South") puts both cities in the North region.

More examples, NOT and practice exercises are on the page about IF with AND and OR.

Nested IF and IFS

For more than two outcomes you can place a second IF inside value_if_false. A three-tier rating looks like this:

=IF(B2>=50000,"A",IF(B2>=30000,"B","C"))

Excel works from left to right and stops at the first true test, so start with the strictest one. Excel 2019 and Microsoft 365 also offer IFS, which avoids the nested brackets: =IFS(B2>=50000,"A",B2>=30000,"B",TRUE,"C"). The final TRUE catches everything else. How to get the order of the tests right is covered in nested IF and IFS.

Common IF mistakes

Missing quotes around text

Write =IF(B2>=C2,met,missed) and Excel treats the words as names and returns #NAME?. Every piece of text in the formula needs straight double quotes. Numbers are the opposite: B2>"10000" compares a number with text and is false for every number, so leave the quotes off.

Comparing in the wrong direction

A flipped operator reverses the result and no error appears. B2<C2 instead of B2>=C2 marks exactly the wrong branches as successful. The edge matters too: with >10000, someone at exactly 10,000 gets nothing. Always test the formula with a value that sits right on the boundary.

Too many nested levels

Excel allows up to 64 nested IFs, but three or four levels are already hard to read and easy to break. One misplaced bracket and a tier is never reached. Switch to IFS, or put the thresholds in a small table and look up the result with VLOOKUP.

Leaving out value_if_false

=IF(B2>10000,B2*5%) shows the word FALSE for smaller amounts instead of 0. Always give the third argument, even if it is just 0 or "" for an empty-looking cell.

IF in German Excel

In a German Excel IF is called WENN, and arguments are separated by semicolons:

English	German
=IF(B2>=C2,"Target met","Target missed")	=WENN(B2>=C2;"Ziel erreicht";"nicht erreicht")
AND / OR	UND / ODER
IFS	WENNS
TRUE / FALSE	WAHR / FALSCH

Our exercises accept both spellings. Pick your Excel language below each exercise.

Which Excel versions have IF?

IF, AND and OR exist in every version, including Excel 2016, Excel for Mac and Excel for the web. IFS needs Excel 2019 or later, or Microsoft 365. If you share a file with someone on an older version, nested IF is the safe choice.

Frequently asked questions