Practice free

IF function

IF shows one result when a condition is true and another when it is false. It is the basic decision function and underpins most conditional logic in spreadsheets.

Example

Read it as a sentence: if E5 is 10 or more, show Insure, otherwise show No need.

Practice this in the app

How IF works

IF makes a decision in a cell. Did the student pass? Is the order big enough for free delivery? Is this item worth insuring? You write the test once, and IF gives one answer when it holds and another when it doesn't.

The test is any comparison that comes out TRUE or FALSE: equal to (=), not equal to (<>), greater than (>), at least (>=), less than (<) and at most (<=). Each answer can be text in quotes, a number, a cell or a whole formula.

In the example above, E5 is the cigar's unit value, 10. That is at least 10, so the test is TRUE and the cell shows Insure. Point the same formula at E4, the candle at 2, and the test is FALSE, so it shows No need.

The third argument is optional. Leave it out and a failed test shows the word FALSE, which is rarely what you want on a finished sheet.

Syntax

IF(logical_test, [value_if_true], [value_if_false])

Arguments in [square brackets] are optional.

The test logical_test (required)
The condition to evaluate.
What to show if it's true value_if_true (optional)
Result when the condition is TRUE.
What to show if it's false value_if_false (optional)
Result when the condition is FALSE; omitting it returns FALSE.

Returns: One of two results depending on the test.

More examples

Each picture shows only the columns that formula reads.

  1. Test for a word

    Does the briar pipe go on the pipe shelf?

    Text in a test goes in double quotes. C3 holds Smoking, so the test is TRUE and the answer is Pipe shelf.

  2. Return a calculation instead of text

    What is Holmes's daily fee with 10% added for anyone over 40?

    Either answer can be a formula. Holmes is 41, so the test passes and IF returns his fee of 25 times 1.1, which is 27.5. Anyone 40 or under keeps the plain fee.

  3. Check two conditions at once

    Is the cigar worth 10 or more and still uninsured?

    Put AND inside the test when every condition must hold. The cigar is worth 10 and is not insured, so the answer is Insure now. Use OR when any one condition is enough: the cigar isn't Chemistry, but it is worth 10 or more, so the OR version answers Locked cabinet.

    • =IF(OR(Items!C5="Chemistry",Items!E5>=10),"Locked cabinet","Shelf") Locked cabinet
  4. Choose between three answers

    Should you walk, take a cab or take the train?

    Put a second IF where the false answer goes. Excel checks the tests in order and stops at the first true one. Scotland Yard is 2.8 miles away: not under 1, but under 5, so Cab. The Reichenbach Fall, 620 miles off, fails both tests and gets Train.

    • =IF(Places!E13<1,"Walk",IF(Places!E13<5,"Cab","Train")) Train
  5. Check whether a cell contains a word

    Is this item some kind of pipe?

    IF can't look inside text by itself, so SEARCH does that part. It finds pipe inside Clay pipe and returns its position, ISNUMBER turns that position into TRUE, and IF shows Pipe.

  6. Leave the cell blank

    How do I show nothing when the test fails?

    Give two double quotes with nothing between them as the answer. The candle is worth 2, so the test fails and the cell looks empty.

Watch out for

Text without quotes gives #NAME?

Write Smoking without quotes and Excel looks for a name or function called Smoking, finds none and returns #NAME?. Every piece of text in a formula needs double quotes around it.

  • =IF(Items!C3=Smoking,"Pipe shelf","Elsewhere") #NAME?
  • =IF(Items!C3="Smoking","Pipe shelf","Elsewhere") Pipe shelf

A number stored as text beats every number

The test cell holds 123 typed as text. Compared with 500, it still comes out bigger, so the answer is over 500. In a comparison Excel ranks text above numbers, whatever the text says. Convert the cell to a real number before you test it.

  • =IF(Data!F2>500,"over 500","not over") over 500

Leaving out the third argument shows FALSE

When the test fails and there is no value_if_false, the cell shows FALSE. Give it "" to show nothing, or the answer you want.

  • =IF(Items!E4>=10,"Insure") FALSE

Text tests ignore case

smoking in lower case still matches Smoking. An IF test treats upper and lower case as the same letters.

  • =IF(Items!C3="smoking","Pipe shelf","Elsewhere") Pipe shelf

An empty cell is both "" and 0

An empty cell passes a test for "" and also a test for 0. To check for a missing entry, test against "": Moriarty has no Home, so this formula shows No address.

  • =IF(Data!F1="","empty","x") empty
  • =IF(Data!F1=0,"zero","x") zero
  • =IF(People!D10="","No address",People!D10) No address

A test on a range gives one answer per row

Point the test at D2:D6 and IF returns five answers that spill down from the formula's cell: Plenty, Low, Plenty, Low, Plenty. Keep the cells below the formula empty so the answers have room.

  • =IF(Items!D2:D6>10,"Plenty","Low") Plenty

Questions people ask

How do you write an IF formula in Excel?

=IF(test, answer if true, answer if false), with any text answer in double quotes. For the cigar, =IF(Items!E5>=10,"Insure","No need") shows Insure.

  • =IF(Items!E5>=10,"Insure","No need") Insure
How do I put two conditions in an IF?

Wrap them in AND when both must be true, or in OR when either one is enough, and use that as the test.

  • =IF(AND(Items!E5>=10,Items!I5=FALSE),"Insure now","OK") Insure now
  • =IF(OR(Items!C5="Chemistry",Items!E5>=10),"Locked cabinet","Shelf") Locked cabinet
How do I write an IF with more than two outcomes?

Nest a second IF in the false slot of the first. The tests run in order, and the first one that is true decides the answer.

  • =IF(Places!E3<1,"Walk",IF(Places!E3<5,"Cab","Train")) Cab
  • =IF(Places!E13<1,"Walk",IF(Places!E13<5,"Cab","Train")) Train
How do I use an IF function with 4 conditions?

Keep nesting: four outcomes need three IFs, each one in the false slot of the one before. IFS does the same job as a flat list of tests and answers.

Does IF work out both answers?

No, only the one it returns. =IF(TRUE,"y",1/0) shows y, even though the false answer would divide by zero.

  • =IF(TRUE,"y",1/0) y

Where it works

Excel
IF is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .