How to use IF in Excel: multiple conditions, nested IF, IFS and AND/OR
Spreadsheet tools · Published
Write IF formulas that check one or several conditions, return text or numbers, and don't break on blanks or errors, with versions for older Excel.
The short answer
IF checks a condition and returns one result when it's true and another when it's false: =IF(B2>=50,"Pass","Fail"). To check several conditions at once, put AND or OR inside IF. To choose between more than two results, nest one IF inside another, or use IFS if you have Excel 2019, Excel 2021, Excel 2024 or Microsoft 365.
How IF works
=IF(logical_test, value_if_true, value_if_false)- logical_test is a comparison that is either TRUE or FALSE, such as
B2>=50,C2="Yes"orD2<>0(not equal to zero). - value_if_true is what the cell shows when the test is TRUE.
- value_if_false is what it shows when the test is FALSE. If you leave it out, Excel shows the word FALSE.
Results can be text, numbers or other formulas. Text goes in double quotation marks ("Pass"); numbers and formulas don't (B2*5%). To show an empty-looking cell, use two quotation marks with nothing between them: "".
Check several conditions with AND and OR
- OR is TRUE if any of its conditions is true.
- AND is TRUE only if all of them are true.
Say shipping is free for orders of $50 or more, or for members. A free gift goes only to members who also spend $50 or more:
=IF(OR(B2>=50,C2="Yes"),"Free","Charge")=IF(AND(B2>=50,C2="Yes"),"Yes","No")| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Order | Total | Member | Shipping | Gift |
| 2 | 1001 | 72 | No | Free | No |
| 3 | 1002 | 35 | Yes | Free | No |
| 4 | 1003 | 35 | No | Charge | No |
| 5 | 1004 | 120 | Yes | Free | Yes |
Example 1: grade bands with nested IF or IFS
When there are more than two outcomes, put another IF in the value_if_false slot. Excel checks the tests from left to right and stops at the first one that's TRUE.
- In C2, enter the nested IF below.
- Copy it down the column.
- Or, in Excel 2019 and later, use the IFS version in D2. It lists each test and its result in pairs, and
TRUE,"F"at the end catches everything else.
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F")))=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Student | Score | Grade (nested IF) | Grade (IFS) |
| 2 | Ana | 95 | A | A |
| 3 | Ben | 82 | B | B |
| 4 | Cara | 70 | C | C |
| 5 | Dev | 64 | F | F |
SWITCH for exact values
When you're matching exact values rather than ranges, SWITCH is shorter. It compares one value against a list and returns the result next to the first match, with an optional default at the end. It has the same availability as IFS.
=SWITCH(A2,"Mugs","Kitchen","Caps","Clothing","Other")Example 2: flag overdue invoices with TODAY()
TODAY() returns the current date, so the flag updates by itself each day. This formula first checks whether the invoice has a payment date, then whether the due date has passed:
=IF(C2<>"","Paid",IF(B2<TODAY(),"Overdue","Not due"))| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Invoice | Due date | Paid on | Status |
| 2 | INV-201 | 9/15/2026 | 9/12/2026 | Paid |
| 3 | INV-202 | 9/20/2026 | Overdue | |
| 4 | INV-203 | 10/10/2026 | Not due | |
| 5 | INV-204 | 9/30/2026 | Overdue |
C2<>"" means "C2 isn't empty". The due dates must be real dates, not text; if they're left-aligned and a date format changes nothing, they're probably text.
Example 3: commission tiers
Results can be calculations. Here, sales of $50,000 or more earn 5%, sales from $20,000 earn 3%, and anything less earns nothing:
=IF(B2>=50000,B2*5%,IF(B2>=20000,B2*3%,0))| Row | A | B | C |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Kim | 62000 | 3100 |
| 3 | Luis | 35000 | 1050 |
| 4 | Mia | 12000 | 0 |
| 5 | Noah | 50000 | 2500 |
For tiers that change often, consider a small rate table and an approximate-match lookup instead, as shown in VLOOKUP vs XLOOKUP vs INDEX/MATCH.
Blank cells
To leave the result empty until data is entered, test for a blank first: =IF(B2="","",B2*2). Checking B2="" is usually safer than ISBLANK. ISBLANK is TRUE only for a truly empty cell, so it returns FALSE for a cell whose formula shows "", while =B2="" is TRUE for both.
Hide errors with IFERROR and IFNA
IFERROR returns your own value when a formula gives any error. For example, =IFERROR(B2/C2,0) shows 0 instead of #DIV/0! when C2 is zero. IFNA only replaces #N/A, which is what lookups return when nothing is found:
=IFNA(MATCH("Hats",A2:A3,0),"Not listed")Here MATCH looks for "Hats" in a product list (A2:A3) that doesn't contain it, so the cell shows "Not listed" instead of #N/A. IFNA is the safer choice for lookups because other errors, such as a typo that gives #NAME?, still show up. Microsoft lists IFNA for Excel 2016 and later.
Common mistakes
| Problem | Cause | Fix |
|---|---|---|
| Always FALSE for numbers | Quotes around a number: B2>="90" compares with text, so 95 isn't >= "90". | Remove the quotes: B2>=90. |
| Wrong band for high values | Tests in the wrong order: IF(B2>=70,…) catches 95 before the >=90 test is reached. | Put the strictest test first (>=90, then >=80, then >=70). |
| #NAME? | Text result without quotes (Pass instead of "Pass"), a misspelled function, or IFS/SWITCH in Excel 2016 or earlier. | Add the quotes, check the spelling, or use nested IF. |
| #VALUE! | Arithmetic on text, such as a number typed with a space or letter. | Clean the source cells or wrap the math in IFERROR. |
| Formula shows as text | The cell was formatted as Text before you typed the formula. | Format the cell as General, then re-enter the formula. |
To count or add up rows that meet several conditions instead of labeling them, see SUMIFS and COUNTIFS.
In short
Start with a plain IF, add AND or OR when a result depends on more than one thing, and nest IFs (or use IFS in newer Excel) for bands, with the strictest test first. Test blanks with ="", and use IFNA or IFERROR to replace errors with something readable. For converting and viewing Excel and CSV files, see Filecon's spreadsheet tools.