The IF Function in Excel: Formulas, Examples and Nested IFs
Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and Excel 2019
IF is the function that lets a spreadsheet make decisions. It checks whether something is true, then shows one result if it is and a different result if it is not. Once you understand IF you can flag overdue invoices, award commission only above a target, label students as pass or fail, or stop a formula from showing an ugly error. This guide starts with the simplest IF formula and builds up to multiple conditions, nested IFs and the newer IFS function.
How the IF function works
The IF function has three parts, called arguments:
=IF(logical_test, value_if_true, value_if_false)
- logical_test is a question Excel can answer with TRUE or FALSE, for example
B2>=50. - value_if_true is what the cell shows when the answer is TRUE.
- value_if_false is what the cell shows when the answer is FALSE.
Suppose column B holds exam marks and the pass mark is 50. In C2 type =IF(B2>=50,"Pass","Fail") and press Enter. Then double-click the fill handle (the small square at the bottom-right of the cell) to copy the formula down the column. Every row now shows Pass or Fail, and the result updates automatically if a mark changes.
Returning text, numbers and calculations
The true and false parts can be text, numbers, cell references or even other formulas:
- Text must be inside double quotes:
=IF(C2="Paid","Closed","Open"). - Numbers are typed without quotes:
=IF(D2>10000,500,0)pays a bonus of 500 when sales exceed 10,000. - Calculations work too:
=IF(D2>10000,D2*5%,D2*2%)pays 5% commission above the target and 2% below it. - A blank result is two quotes with nothing between them:
=IF(E2="","",E2*1.05)leaves the cell empty until a value is entered in E2.
If you leave out value_if_false completely, Excel shows the word FALSE when the test fails. It is usually better to give an explicit result, even if it is just "" or 0.
Comparison operators you can use
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | A2="Dubai" |
| <> | Not equal to | A2<>"Dubai" |
| > | Greater than | B2>100 |
| < | Less than | B2<100 |
| >= | Greater than or equal to | B2>=100 |
| <= | Less than or equal to | B2<=100 |
Text comparisons are not case-sensitive, so "dubai" and "DUBAI" are treated as equal. To compare dates, compare against another cell or use the DATE function, for example =IF(C2<TODAY(),"Overdue","On time") or =IF(C2>DATE(2026,12,31),"Next year","This year").
Testing more than one condition with AND and OR
Often a decision depends on two or more things at once. Put AND or OR inside the logical test:
- AND returns TRUE only when every condition is true.
=IF(AND(B2>=50,C2>=75%),"Eligible","Not eligible")requires a mark of at least 50 and attendance of at least 75%. - OR returns TRUE when at least one condition is true.
=IF(OR(D2="Gold",E2>20000),"Free delivery","Standard")gives free delivery to Gold customers or to any order above 20,000. - NOT reverses a test:
=IF(NOT(ISBLANK(F2)),"Done","Pending").
Nested IF formulas
When there are more than two possible results, you can place one IF inside another. Excel reads the tests from left to right and stops at the first one that is true. A grading formula might look like this:
=IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C",IF(B2>=50,"D","F"))))
The order matters. If you tested B2>=50 first, a mark of 95 would stop there and receive a D. Always start with the strictest condition and work down. Count your brackets: each IF you open needs its own closing bracket at the end. Excel colours matching pairs of brackets while you type, which helps you spot a missing one.
Nested IFs become hard to read after three or four levels. For longer lists of bands, consider IFS (below) or a small lookup table with XLOOKUP or VLOOKUP using approximate match. A lookup table is also easier to change later, because you edit the table rather than the formula.
The IFS function (Excel 2019 and later)
IFS removes the need for nesting. You list pairs of test and result:
=IFS(B2>=90,"A",B2>=75,"B",B2>=60,"C",B2>=50,"D",TRUE,"F")
The final pair TRUE,"F" acts as a catch-all for anything that did not match an earlier test. Without it, a value that matches nothing returns the #N/A error. IFS is not available in Excel 2016 or earlier, so if you share files with people on older versions, stay with nested IF.
Hiding errors with IFERROR
IFERROR is a shortcut for "if this formula produces an error, show something else instead". It is useful around divisions and lookups:
=IFERROR(C2/B2,0)shows 0 instead of #DIV/0! when B2 is empty.=IFERROR(VLOOKUP(A2,Prices,2,FALSE),"Not found")shows a friendly message when a code is missing from the price list.
Use IFERROR carefully. It hides every kind of error, including mistakes in your formula, so check that the formula works correctly before wrapping it. When you only want to catch missing lookups, IFNA is safer because it catches #N/A and lets other errors show. Our guide to fixing common Excel errors explains each error message.
Practical examples to try
- Overdue invoices:
=IF(AND(E2="Unpaid",D2<TODAY()),"Chase","") - Stock reorder flag:
=IF(C2<=D2,"Reorder","OK")where C is stock on hand and D is the reorder level. - VAT only for UAE customers:
=IF(B2="UAE",C2*5%,0) - Counting results: after adding a Pass/Fail column,
=COUNTIF(C:C,"Pass")counts how many passed. See SUMIFS and COUNTIFS for more.
Common mistakes to avoid
- Forgetting quotes around text.
=IF(A2=Paid,...)gives #NAME? because Excel thinks Paid is a named range. - Putting numbers in quotes.
=IF(B2>"50",...)compares against text and gives wrong answers. - Wrong order in nested IFs. Test the highest band first.
- Typing percentages inconsistently. 75% and 0.75 are the same value, but 75 is not.
- Extra spaces in data. "Paid " with a trailing space does not equal "Paid". Clean the data with TRIM; see Excel text functions.
Summary
- IF checks a condition and returns one result for TRUE and another for FALSE.
- Wrap text in quotes; leave numbers and cell references unquoted.
- Combine IF with AND, OR and NOT for multiple conditions.
- Order nested IFs from the strictest test down, or use IFS in newer versions.
- Use IFERROR or IFNA to replace error messages with something clearer.
Back to: all Excel tutorials.