IF Function in Excel: Ultimate Guide

IF is Excel’s branching function. It evaluates a condition — the logical test — and then chooses between two outcomes. The condition must resolve to either TRUE or FALSE; there is no third state and no “maybe”.

That binary nature is the source of both its power and its limits. It is excellent for decisions with two outcomes: pass or fail, over budget or under, in stock or out. It becomes awkward the moment a decision has three or more possible outcomes, which is why nested IF exists and why IFS was added to Excel in 2019.

How IF evaluates a formula

Take the formula =IF(A2>=60, "Pass", "Fail"). Excel reads it in three steps:

1. Evaluate the logical test. It computes A2>=60 and produces either TRUE or FALSE. Nothing else is looked at yet.
2. Pick the matching branch. If the test is TRUE, Excel reads the second argument. If FALSE, the third.
3. Return that value. Only the chosen branch is evaluated — the other one is never computed, which matters if one branch contains an expensive lookup or a formula that could error.

The full signature has three arguments, and only the last one is optional.

=IF(logical_test, value_if_true, [value_if_false])

Walking through each argument

1. logical_test — any expression that evaluates to TRUE or FALSE. Usually a comparison such as A2>=60, B2="", or C2<>0.
2. value_if_true — what to return when the test is TRUE. Can be a number, text in double quotes, a cell reference, or another formula.
3. value_if_false (optional) — what to return when the test is FALSE. If you omit it, Excel returns the logical value FALSE.

The logical test is built from comparison operators. These are the six you will use in almost every IF formula you ever write.

Boundary values — where most bugs live

The single most common off-by-one bug in an IF formula is choosing > when you meant >=. The two differ only at the exact threshold, which is precisely the case that gets tested in reports.

These are the patterns that come up over and over in actual workbooks. Each one includes a spreadsheet mockup showing exactly what the data looks like and the formula that goes in each cell.

Example 1 — Payroll: overtime pay calculation

Pay the standard rate up to 40 hours, and 1.5× the rate for any hours beyond 40.

' Regular pay — cap hours at 40
=IF(B2>40, 40*C2, B2*C2)

' Overtime pay — hours beyond 40, at 1.5x
=IF(B2>40, (B2-40)*C2*1.5, 0)

' Total pay
=D2+E2

Example 2 — Student grade tracker with a nested IF

The classic use of nested IF: convert a numeric score into a letter grade.

' Nested IF — most restrictive test first
=IF(B2>=90,"A",
   IF(B2>=80,"B",
      IF(B2>=70,"C",
         IF(B2>=60,"D","F"))))

' Simple pass/fail alongside it
=IF(B2>=60,"Pass","Fail")

Example 3 — Sales commission tiers

Commission is a percentage that changes as sales cross thresholds.

Example 4 — Invoice aging report

Accounts receivable reports bucket invoices by how overdue they are.

Example 5 — Data quality check

When you import data, you need a single flag that says whether a row is usable.

A nested IF puts one IF function inside another, in the value_if_false slot. It is how Excel handled multi-way decisions before IFS existed, and it still works perfectly well — it just gets hard to read quickly.

IFS was introduced in Excel 2019 and Microsoft 365. It takes pairs of condition and result, reads top to bottom, and returns the first match. The final TRUE,"F" pair acts as a catch-all.

' 1. Nested IF — works everywhere, gets ugly fast
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

' 2. IFS — flat, readable, modern Excel only
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")

' 3. VLOOKUP with approximate match — thresholds in a table
=VLOOKUP(B2,$F$2:$G$6,2,TRUE)

' 4. XLOOKUP with approximate match — modern syntax
=XLOOKUP(B2,$F$2:$G$6,$G$2:$G$6,,-1)

A single comparison is often not enough. That is what AND, OR, and NOT are for — and they go inside the logical test, not around the IF.

' AND — every condition must be true
=IF(AND(B2>=60,C2>=60),"Pass both","Retake one")

' OR — at least one condition must be true
=IF(OR(B2>=90,C2>=90),"Honours","Standard")

' NOT — invert a condition
=IF(NOT(ISBLANK(B2)),"Has value","Empty")

' Combined — either condition, but not both
=IF(OR(AND(B2>=60,C2<60),AND(B2<60,C2>=60)),"Retake one","Review")

AND, OR, NOT at a glance

Common mistakes with AND and OR

Text comparisons are where IF gets subtle. The = operator is case-insensitive, does not support wildcards, and treats “East” and “East ” (with a trailing space) as different values. Each of these catches people at some point.

Case-insensitive vs case-sensitive

Wildcards — where they work and where they do not

The = operator inside IF does not support wildcards. That is a common surprise for people who use SUMIF and COUNTIF, which do support wildcards.

' Exact text match (case-insensitive)
=IF(A2="East","Yes","No")

' Case-sensitive match
=IF(EXACT(A2,"EAST"),"Yes","No")

' Contains "East" anywhere (case-insensitive)
=IF(ISNUMBER(SEARCH("East",A2)),"Contains","Not found")

' Starts with "North"
=IF(LEFT(A2,5)="North","Northern","Other")

' Ends with "Ltd"
=IF(RIGHT(A2,3)="Ltd","Company","Other")

' Blank cell
=IF(A2="","Empty","Has value")

' Trailing spaces often cause false matches — clean first
=IF(TRIM(A2)="East","Yes","No")

Dates are numbers underneath. A comparison like A2>TODAY() works exactly like a numeric comparison — but only if the cell really contains a date and not a text string that looks like one.

Common date comparisons

' Overdue?
=IF(A2=TODAY(),A2<=TODAY()+7),"Due soon","Later")

' Same month and year as today
=IF(AND(YEAR(A2)=YEAR(TODAY()),MONTH(A2)=MONTH(TODAY())),"This month","Other")

' Older than 30 days
=IF(TODAY()-A2>30,"Ageing","Fresh")

' Weekend check
=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

' Quarter label
=IF(MONTH(A2)<=3,"Q1",IF(MONTH(A2)<=6,"Q2",IF(MONTH(A2)<=9,"Q3","Q4")))

Date functions that pair with IF

IF rarely works alone. In real workbooks it wraps lookups, guards divisions, and validates inputs before passing them to another function. The three functions below are the ones you will reach for most often.

IFERROR — the universal safety net

IFERROR(value, value_if_error) returns the first argument if it works and the second if it produces any error. It replaces the older, clunkier IF(ISERROR(...),...) pattern.

' Guarding a division by zero
=IFERROR(A2/B2,"Check inputs")

' Guarding a lookup that finds nothing
=IFERROR(VLOOKUP(A2,Products,3,FALSE),"Not found")

' Guarding a nested formula
=IFERROR(IF(A2>0,A2/B2,0),"Check inputs")

IFNA — only catches missing lookups

IFNA catches #N/A and nothing else. Use it when you want a lookup miss to show a friendly message, but you still want other errors (typos, bad references) to surface so you notice them.

' IFNA — only handles missing lookups, other errors still show
=IFNA(VLOOKUP(A2,Products,3,FALSE),"Not found")

' IFERROR — hides every kind of error
=IFERROR(VLOOKUP(A2,Products,3,FALSE),"Not found")

IF combined with a lookup — driving a result from a table

Rather than nesting five IFs to pick a value, put the values in a small table and let a lookup do the choosing. IF then handles the edge cases around the lookup.

IF logic does not only live in cells. Data Validation rules and Conditional Formatting rules both use the same TRUE/FALSE thinking, and the same operators you already know from IF. Learning to spot these patterns makes both features much less mysterious.

Data Validation — reject bad input

Data Validation rules run a hidden logical test. If the test returns TRUE, the input is accepted. If FALSE, Excel rejects it. Everything you know about IF logical tests applies directly.

1. Select the cells you want to validate, for example a whole column of scores.
2. Open Data → Data Validation. Choose Custom from the Allow list.
3. Type the logical test — the same condition you would put in an IF. Do not write =IF(...); just the test itself.

' Score must be between 0 and 100
=AND(A2>=0, A2<=100)

' Email must contain "@"
=ISNUMBER(SEARCH("@", B2))

' Date must not be in the past
=A2>=TODAY()

' Text must be one of three allowed values
=OR(C2="Open", C2="Closed", C2="Pending")

' Discount must not exceed the price
=D2<=C2

Conditional Formatting — highlight rows that match a rule

Conditional Formatting also runs a hidden logical test, cell by cell, and applies formatting wherever the test is TRUE. The same six operators, the same AND/OR logic, the same trap of testing the wrong range.

' Highlight the whole row when status is "Overdue"
=$D2="Overdue"

' Highlight scores below 60
=A2<60

' Highlight dates falling in the next 7 days
=AND(A2>=TODAY(), A2<=TODAY()+7)

' Highlight duplicates across two columns
=COUNTIF($A$2:$A$100,$A2)>1

' Alternate row shading — every even row
=MOD(ROW(),2)=0

Most IF problems fall into one of a handful of patterns. Each one below has a known cause and a known fix.

' Without protection
=IF(A2>0,A2/B2,0)

' With protection
=IFERROR(IF(A2>0,A2/B2,0),"Check inputs")

' Lookup that finds nothing
=IFERROR(VLOOKUP(A2,Products,3,FALSE),"Not found")

' Only catch missing lookups, not other errors
=IFNA(VLOOKUP(A2,Products,3,FALSE),"Not found")

These habits keep IF formulas understandable months later, and working when a colleague inherits the file.

Try these before opening the answers. They use the examples from earlier in the guide.

1. Q1. Cell A1 contains 75. What does =IF(A1>=60,"Pass","Fail") return?
   Answer: Pass. 75 is greater than or equal to 60, so the second argument is returned.
2. Q2. Cell A1 contains 45. What does =IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","F")))) return?
   Answer: F. 45 fails every test down to the catch-all.
3. Q3. Cell A1 contains 15000. What rate does =IF(A1>25000,0.12,IF(A1>10000,0.08,0.05)) return?
   Answer: 0.08. 15000 is not above 25000 but it is above 10000.
4. Q4. What does =IF(AND(5>3,2>4),"Yes","No") return?
   Answer: No. AND requires both conditions to be TRUE. 2>4 is FALSE, so the whole test is FALSE.
5. Q5. What does =IF(OR(10>5,1>2),"OK","BAD") return?
   Answer: OK. OR requires only one condition to be TRUE. 10>5 is TRUE.
6. Q6. Why does =IF(A2="East","Yes","No") return "No" when A2 visually shows "East"?
   Answer: Most likely there are trailing or leading spaces. Excel treats "East" and "East " as different. Wrap A2 in TRIM(): =IF(TRIM(A2)="East","Yes","No").

• IF tests a condition and returns one value for TRUE and another for FALSE.
• The syntax is =IF(logical_test, value_if_true, [value_if_false]).
• Text values must be wrapped in double quotes or you get #NAME?.
• Always supply the third argument — omitting it returns the word FALSE.
• >= includes the boundary, > excludes it.
• In nested IF, order tests from most restrictive to least.
• Past three levels, switch to IFS — it is flat and far easier to audit.
• Put AND and OR inside the logical test, not around the IF.
• Guard against blanks and non-numbers with ISBLANK and ISNUMBER.
• Use IFERROR to replace raw errors with readable messages — but add it last.
• For banded thresholds, a lookup table beats a nested IF every time.
• The = operator is case-insensitive. Use EXACT for case-sensitive matches.
• IF does not support wildcards. Use SEARCH or ISNUMBER for contains checks.
• Clean text with TRIM() — trailing spaces break text comparisons silently.
• Date comparisons only work on real dates. Verify with ISNUMBER().
• The same logical tests drive Data Validation and Conditional Formatting rules.
• Break long formulas across lines with Alt+Enter.

IF is the gateway to Excel's whole logical and lookup family. Once it is comfortable, the natural next steps are the functions that replace it in more complex workbooks.

Start with the logical functions that pair with IF — ifs-function, and-or-functions, and iferror-function.

From there, move into lookups and conditional sums: xlookup-function, vlookup-function, sumif-function, and sumifs-function.