The IF function tests a condition and returns one result when it is TRUE and another when it is FALSE. Use it to label values, calculate different results, or check whether a record meets a rule.
Simple IF Functionality
An IF formula has a logical test, a result for TRUE, and an optional result for FALSE. Start the formula with an equals sign when you enter it in a worksheet cell:
IF(logical_test, value_if_true, [value_if_false])
The first argument in the function is the logical test, which can be any expression that results in a true or false value. The second argument is the value that will be returned if the logical test is true, and the third argument is the value that will be returned if the logical test is false.
For example, suppose you want to label amounts of 300 or more as “High” and amounts below 300 as “Low”. With a numeric amount in B2, use:
=IF(B2>=300, “High”, “Low”)
The test B2>=300 checks whether B2 is greater than or equal to 300. If it is, IF returns “High“; otherwise, it returns “Low“. Exactly 300 returns High because the test includes equality.
Enter a number in B2, select C2, type =IF(B2>=300,"High","Low"), and press Enter. To label more rows, fill the formula down from C2. The relative reference B2 becomes B3, B4, and so on.
Text results such as “High” need double quotation marks; numbers and cell references do not. For example, =IF(B2>=300,1,0) returns a number. Use "" for a result that displays no text: =IF(B2>=300,"High",""). Explicitly supply the FALSE result when you want a particular label, number, or empty string.
| Value in B2 | Is B2 >= 300? | Result |
|---|---|---|
| 299 | FALSE | Low |
| 300 | TRUE | High |
| 350 | TRUE | High |
These examples use commas between arguments. If your Excel regional settings use semicolons, enter =IF(B2>=300;"High";"Low") instead.
Advanced IF Functionality: Using AND and OR Criteria
You can also use the IF function in Excel by combining it with the AND and OR functions. AND returns TRUE only when all its conditions are true. OR returns TRUE when at least one condition is true. IF uses that TRUE or FALSE result to choose what to return.
Using the AND Function
The AND function allows you to check multiple conditions at the same time. For example, suppose you have a list of students and their math and English marks, and you want to find out which students have passed the class by getting at least 40 marks in both subjects. To do this, you would use the AND function in the logical test of the IF function, like so:
=IF(AND(C12>=40, D12>=40), “Pass”, “Fail”)
This logical test checks if the value in cells C12 and D12 is greater than or equal to 40, using the AND function. If both conditions are true, the function will return “Pass“. Otherwise, it will produce a “Fail“.
Using the OR Function
The OR function allows you to check if at least one of several conditions is true. For example, suppose you want to find out which students have passed the class by getting at least 40 marks in either math or English. To do this, you would use the OR function in the logical test of the IF function, like so:
=IF(OR(C12>=40, D12>=40), “Pass”, “Fail”)
This logical test checks if the value in cells C12 and D12 is greater than or equal to 40 using the OR function. If at least one of the conditions is true, the function will return “Pass“; otherwise, it will return “Fail“.
| Math marks | English marks | IF with AND | IF with OR |
|---|---|---|---|
| 45 | 50 | Pass | Pass |
| 45 | 35 | Fail | Pass |
| 35 | 35 | Fail | Fail |
Conclusion
Choose a logical test that matches your rule, then supply the two results you want. Check a value at the cutoff before filling the formula down. For multiple conditions, use AND when every condition must be true and OR when any one condition is enough. The video tutorial also covers nested IF formulas.
