Excel IF function: return one value or another
IF checks a test and returns one value when it's true and another when it's false. =IF(B2>D2,"Yes","No") shows Yes when the sale in B2 beats the target in D2.
The examples use this small sales table, the same one in the practice box below.
| A Rep | B Sales | D Target | E Region | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | 1,000 | East |
| 3 | Luis | 830 | 1,000 | West |
| 4 | Priya | 2,100 | 1,000 | East |
| 5 | Marcus | 475 | 1,000 | North |
| 6 | Jen | 990 | 1,000 | West |
| 7 | Omar | 1,600 | 1,000 | East |
Syntax
=IF(logical_test, value_if_true, value_if_false)Examples
=IF(B2>D2,"Yes","No")returns Yes for Dana, since 1,250 beats 1,000.=IF(B2>D2,B2*0.1,0)returns a 10% bonus when she beats target, otherwise 0. That's 125 for Dana.
Comparison operators
> greater than, < less than, >= at least, <= at most, = equal to, <> not equal to.
Two tests with AND or OR
=IF(AND(B2>D2,E2="East"),"Bonus","") needs both to be true. Swap in OR when either one is enough.
Nested IFs
=IF(B2>=2000,"Gold",IF(B2>=1000,"Silver","Bronze")) checks from the top down and stops at the first true test. Order matters: put the highest bar first.
Common mistakes
- No quotes around text.
=IF(B2>D2,Yes,No)gives #NAME?. Text results need quotes. Numbers don't. - Typing the number instead of the cell.
=IF(B2>1000,...)works until the target changes. Point to D2. - Wrong order in nested IFs. Testing >=1000 before >=2000 labels Priya Silver instead of Gold, because her 2,100 passes the first test.
Try it: Hit target?
In C2, show Yes if Dana's sale (B2) is above her target (D2), otherwise No.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
How many IFs can I nest?
Excel allows up to 64, but more than three gets hard to read. Newer Excel and Google Sheets also have IFS for long chains.
How do I return a blank cell?
Use two quotes with nothing between them, like =IF(B2>D2,"Yes","").
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use IFERROR in Excel to replace #N/A, #DIV/0! and other errors, when not to use it, and a live exercise with VLOOKUP.
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
How to use SUMIF in Excel, the argument order that trips people up, using cell references in the condition, and a live exercise.