IF function
IF checks a condition and returns one value when it is true and another when it is false. It is the building block for flags, tiers, bonuses and any rule you can phrase as "if this, then that". Combine it with AND or OR for several conditions, or move to IFS when there are more than two outcomes.
- Available in
- Every current version
- Category
- Logical functions
- Returns
- Whichever of the two values matches the result of the test.
- Google Sheets
- Works the same way in Google Sheets.
Syntax
=IF(logical_test, value_if_true, [value_if_false])
| Argument | Required? | What it is |
|---|---|---|
logical_test | Required | A comparison that is TRUE or FALSE, such as B2>=100 or C2="Paid". |
value_if_true | Required | What to return when the test is TRUE: a number, text in quotes, a cell or another formula. |
value_if_false | Optional | What to return when the test is FALSE. Left out, IF returns FALSE. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =IF(B2>=200,"Free shipping","Standard") | Free shipping | Text result based on a number test |
| E3 | =IF(C3="No",B3*0.05,0) | $4.25 | A calculation when the test is TRUE: a 5% late fee on an unpaid order |
| E4 | =IF(AND(B4>500,C4="No"),"Call customer","") | Call customer | Two conditions with AND; "" leaves the cell blank when the test fails |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Order | Total | Paid | Result | |
| SO-1001 | $240 | Yes | Free shipping | |
| SO-1002 | $85 | No | $4.25 | |
| SO-1003 | $510 | No | Call customer | |
| SO-1004 | $130 | Yes |
Practice IF
From lesson 5, The IF function. Graded instantly, then re-checked on changed data.
Pass or fail
C2, show Pass if the score is 70 or higher, otherwise Fail. Fill down to C7.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NAME? | Text results need double quotes: "Yes", not Yes. Without them Excel looks for a named range called Yes. |
| A test that should match but doesn't | C2="Yes" is FALSE when the cell holds "Yes " with a trailing space. Clean the column with TRIM, or test TRIM(C2)="Yes". |
| Tiers in the wrong order | In nested IFs, test the biggest threshold first: =IF(B2>=1000,"Gold",IF(B2>=500,"Silver","Bronze")). Testing >=500 first labels every 1,000+ customer Silver. |
| Writing a between test the math way | =IF(10<B2<20,...) is always FALSE in Excel. Split it: =IF(AND(B2>10,B2<20),...). |
Tips
- Return "" to show a blank cell instead of 0 or FALSE.
- Compare against a cell, such as B2>=$H$1, instead of typing the threshold, so it can be changed in one place.
Questions
How many IFs can you nest in Excel?
Up to 64, but anything past three or four is hard to check. Use IFS, SWITCH or an XLOOKUP against a small table instead.
How do I use IF with two conditions?
Put AND or OR inside the test: =IF(AND(B2>100,C2="Paid"),"OK","Check") needs both conditions, and OR needs either one.
Why does my IF formula return FALSE?
The test was FALSE and value_if_false was left out. Add a third argument, such as "" for a blank.
Lessons that use IF
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.