Logic and error handling

Excel IF Function: Syntax, Nesting and Worked Examples

Returns one value when a condition is true and another when it is false.

IF is the function that makes a spreadsheet decide something. You give it a question with a yes-or-no answer, a result to use when the answer is yes, and a result to use when it is no. Did this rep hit their target? Is this invoice overdue? Is this order big enough for free delivery?

The condition is any expression that evaluates to TRUE or FALSE, which in practice means a comparison: B2>C2, A2="North", D2>=1000. The two results can be anything — text, a number, a cell reference, or another formula entirely.

That last point is where IF gets powerful and where it gets ugly. Because a result can be another IF, you can chain decisions together. Three or four levels deep, though, and the formula becomes unreadable; that is the moment to reach for IFS, SWITCH, or a small lookup table instead.

Syntax

=IF(logical_test, value_if_true, value_if_false)

Arguments

logical_test
Required
Anything that evaluates to TRUE or FALSE — usually a comparison using =, <>, >, <, >= or <=.
value_if_true
Required
What to return when the test passes. Text must be in quotes.
value_if_false
Optional
What to return when it fails. Omitting it returns the literal FALSE, which is almost never what you want — pass "" for a blank instead.

The example data

Headers in row 1, data in A2:C5.

ABC
1RepSalesTarget
2Alice12401000
3Bruno3851000
4Chen21001500
5Dana9400

Worked examples

=IF(B2>C2, "Met", "Missed")

Result: Met

Alice sold 1240 against a target of 1000, so the test passes.

=IF(B3>=C3, "Met", "Missed")

Result: Missed

Bruno's 385 is below 1000, so the false branch runs.

=IF(B2>C2, B2-C2, 0)

Result: 240

The results do not have to be text. This returns the amount over target, or zero.

=IF(B2>=2000, "Gold", IF(B2>=1000, "Silver", "Bronze"))

Result: Silver

A nested IF. The second IF is the false branch of the first, so it only runs when sales are under 2000.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: Basic IF Function

Common errors and how to fix them

Returns FALSE in the cell

Why it happens: The third argument was left out and the test failed.

How to fix it: Supply a third argument. Use "" if you want the cell to look empty.

#VALUE!

Why it happens: The test compares incompatible types, or a branch does arithmetic on text.

How to fix it: Check that the cells being compared hold the kind of data you expect. A number stored as text will not compare numerically.

Always returns the true branch

Why it happens: The test uses a single = as assignment-looking syntax on text without quotes, or compares against a value that is always present, such as =IF(A2, ...) where A2 holds any non-zero value.

How to fix it: Make the test an explicit comparison: =IF(A2="North", ...) rather than =IF(A2, ...).

Nested IF returns the wrong band

Why it happens: The conditions are in the wrong order, so a broader one catches values before the narrower one is reached.

How to fix it: Order nested conditions from most restrictive to least — test >=2000 before >=1000, never the other way round.

Tips worth knowing

  • Text results need quotes; numbers and cell references do not.
  • Use "" for an empty-looking result: =IF(B2>C2, B2-C2, "").
  • Combine conditions with AND and OR rather than nesting: =IF(AND(B2>C2, A2="North"), ...).
  • Once you are three IFs deep, switch to IFS or a lookup table. Nested IFs are correct but nobody, including you next month, can read them.

Frequently asked questions

How many IFs can I nest?

Excel allows 64 levels, but readability collapses long before that. Beyond three, use IFS for a flat list of conditions, SWITCH when testing one value against several options, or VLOOKUP against a small band table.

How do I test two conditions at once?

Wrap them in AND for "both must be true" or OR for "either will do": =IF(AND(B2>C2, A2="North"), "Yes", "No"). This is far easier to read than an IF inside an IF.

How do I make IF return a blank cell?

Return an empty string with "". The cell will look empty, though it technically contains a formula result — so ISBLANK will still report FALSE for it, and COUNTA will still count it.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions