IF Statements in Excel: AND, OR and Nested IF With Examples

IF lets a spreadsheet make decisions. Combined with AND and OR it can test several conditions at once. This guide shows the syntax and formulas you can paste straight into your own sheet.

Updated 18 Nov 2026 · By Softotech Solutions

IF function syntax

The formula is =IF(logical_test, value_if_true, value_if_false). Excel checks the test. If it is true you get the second argument, otherwise the third. Example: =IF(B2>=50,"Pass","Fail") returns Pass when B2 is 50 or more.

Comparison operators: = equal, <> not equal, > greater than, < less than, >= greater than or equal, <= less than or equal. Text in a formula must be inside double quotes.

IF with AND

AND returns true only when every condition is true. =IF(AND(B2>=50,C2>=50),"Pass","Fail") passes a student only if both scores are 50 or higher. The AND function can hold up to 255 conditions, for example =AND(A2>0,A2<100) to check a value is between 0 and 100.

IF with OR

OR returns true when at least one condition is true. =IF(OR(B2="Gold",B2="Platinum"),"Discount","No discount") gives a discount to either of two customer levels. Use OR when any one match is enough.

Nested IF statements

A nested IF places one IF inside another to handle more than two outcomes. A grading example:

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

Excel reads left to right and stops at the first true test, so list the highest threshold first. If you reverse the order, a score of 95 would match the first test it meets and give the wrong grade. Each IF needs its own closing bracket, so the number of closing brackets at the end must equal the number of IFs.

In Excel 2019 and Microsoft 365 the IFS function is easier to read: =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F"). The final TRUE acts as a catch-all.

IF contains: test for text inside a cell

IF cannot search inside text by itself. Combine it with SEARCH and ISNUMBER: =IF(ISNUMBER(SEARCH("paid",A2)),"Yes","No"). SEARCH returns a position number when the word is found, or an error when it is not, and ISNUMBER turns that into true or false. SEARCH ignores case. Use FIND if you need a case-sensitive match.

IF with blank cells, numbers and dates

Test for an empty cell with =IF(A2="","Missing","OK"). Compare against a date with =IF(D2<TODAY(),"Overdue","On time"). Return a number instead of text, for example a bonus: =IF(C2>=10000,C2*0.05,0) pays 5 percent on sales of 10,000 or more.

Common mistakes

#NAME? error. Text without quotes, or a misspelt function name. Always FALSE. A number stored as text, so 50 does not match “50”. Wrong grade. Thresholds listed in the wrong order. Too many nested levels. If a formula runs past four or five IFs, use IFS, a lookup table with VLOOKUP, or XLOOKUP.

For counting and summing with conditions see COUNTIFS and SUMIFS.

Real-world IF examples

Here are formulas that small businesses use every week. Replace the cell references with your own.

TaskFormula
Late fee if an invoice is overdue=IF(AND(D2<TODAY(),E2="Unpaid"),C2*0.05,0)
Free shipping above a threshold=IF(B2>=75,0,6.95)
Commission tiers=IF(B2>=50000,B2*0.1,IF(B2>=20000,B2*0.07,B2*0.04))
Flag missing data=IF(OR(A2="",B2="",C2=""),"Incomplete","OK")
Status from a due date=IF(D2="","",IF(D2<TODAY(),"Overdue","On track"))

The amounts in these examples are illustrative. Change the rates and thresholds to match your own policies.

IF with IFERROR, ISBLANK and other helpers

IF works best with helper functions. ISBLANK(A2) tests for an empty cell, ISNUMBER(A2) checks that a value is a number, and IFERROR(formula,"") replaces an error with something readable. A common pattern is =IF(B2=0,"",A2/B2), which avoids a #DIV/0! error by leaving the result blank when the divisor is zero. Another is =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","") to mark repeated entries.

IF with SUM, AVERAGE and other functions

You can place any function inside the true or false arguments. =IF(SUM(B2:B6)>100,"Over budget","Within budget") compares a total with a limit, and =IF(AVERAGE(B2:B6)>=70,"Pass","Review") checks an average. To add up values that meet a condition across many rows, switch to SUMIFS instead of filling a helper column of IF results.

IF versus lookup tables

If the same list of thresholds appears in many formulas, put it in a small table and use a lookup so you change it in one place. A grade table with lower limits 0, 60, 70, 80 and 90 and grades F, D, C, B and A works with =VLOOKUP(B2,$F$2:$G$6,2,TRUE). The final argument TRUE tells Excel to find the closest lower limit, which requires the first column to be sorted ascending. This is easier to maintain than a long nested IF and avoids bracket mistakes.

How to debug an IF formula

Select the cell and open Formulas, Evaluate Formula to step through the calculation one part at a time. Check three things first: quotes around text, the order of your thresholds, and whether numbers are really stored as numbers. Press F9 on a highlighted part of the formula in the formula bar to see its value, then press Esc to cancel. If a formula suddenly shows the text instead of a result, the cell is probably formatted as Text.

Frequently asked questions

How do I use IF with multiple conditions?

Wrap the conditions in AND (all must be true) or OR (any can be true), then use that inside IF, for example =IF(AND(A2>0,B2>0),”Yes”,”No”).

How many IFs can you nest in Excel?

Modern Excel allows up to 64 nested IF levels, but formulas with more than a few are hard to read. Use IFS or a lookup table instead.

How do I make IF check if a cell contains text?

Use =IF(ISNUMBER(SEARCH(“text”,A2)),”Yes”,”No”).

What is the difference between IF and IFS?

IF handles one test with two outcomes and can be nested. IFS lists several tests in order without nesting.

How do I write not equal in an IF formula?

Use the <> operator, for example =IF(A2<>“Paid”,”Open”,”Closed”).

Official reference: IF function (Microsoft Support)

Need Excel work done for you?

Talk to us

More free tools: IF formula in Excel · COUNTIFS in Excel · Conditional formatting · All free calculators

Guides: How to Use the AND and OR Functions in Excel · How to Use the TEXT Function in Excel · How to Use IFERROR in Excel