IF Formula in Excel: Everything You Need to Know
The IF formula is one of the most useful formulas in Excel. It is highly adjustable and can be extended by other formulas easily. But many Excel users don’t really know all it’s secrets. Do you know how to use it? This article covers everything you need to know about it: How to use it, how to extend it and how to use the related formulas IFNA, IFERROR and the new formula IFS.
The IF formula
Basics of the IF formula
The IF formula is very popular in Excel. It checks whether a value is true or not. For example, you compare two values. If they are the same, you want Excel to do something. But if the are not equal, Excel does something else. In it’s simplest form, the structure of the IF formula looks like this:
The IF formula has three parts:
- The condition: In this part of the formula you check, if a condition is true. There are countless options here, so that we are going to talk about this part more in detail later on.
- If the condition in (1) is met, Excel will execute this part. That can be a cell reference, a value or even another if formula.
- If on the other hand the check in (1) returns false, then Excel will jump directly to this part of the formula. Instead of just static values (e.g. a text like “The result is greater than 0”), you can of course use formulas as well.
Let’s talk more about the first part, the check. The easiest way is to compare two values: =IF(A1=A2, but there are many other possibilities. For example, you can check if value 1 is larger than 2. The following table provides an overview of the possible comparisons.
| Comparison | Character | Example | Meaning |
| Equal | = | =IF(A1=A2 | If cell A1 equals A2, then … |
| Not equal | <> | =IF(A1<>A2 | If cell A1 is not equal to A2, then … |
| Larger than | > | =IF(A1>A2 | If the value in cell A1 is larger than A2, then … |
| Smaller than | < | =IF(A1<A2 | If the value in cell A1 is smaller than A2, then … |
| Larger than or equal to | >= | =IF(A1>=A2 | If the value in cell A1 is larger than or equals to A2, then … |
| Smaller than or equal to | <= | =IF(A1<=A2 | If the value in cell A1 is smaller than or equals to A2, then … |
The IF formula in Excel
Example for the simple IF formula:
We got the table on the right hand side. We want to know, if the value in cell C5 is greater than 0 or not. How do we do it?
Let’s set up the formula step by step:
- The check: We want to know if cell C5 is greater than 0. So the first part of the formula is “=IF(C5>0”.
- If C5 is greater than 0 – so the condition in part 1 of the IF formula returns TRUE – we want to write “The result is greater than 0”.
- If C5 is not greater than 0 (that means less or equal to 0, the return value should be “The result is not greater than 0”.
The complete formula is:
|
1 |
=IF(C5>0,"The result is greater than 0","The result is not greater than 0") |
So, what is the result of above IF formula in cell C7? Correct, as the result in cell C5 is -10 and therefore less than 0, the output in cell C7 is “The result is not greater than 0”.
Extend the IF formula 1: More than one criteria to check
If you got more than one criteria to check, you can combine them. Let’s say, you want to check if two conditions are true:
If both cells C3 and C4 are greater than 0 (like in the image above). In such case you combine the two conditions with AND:
|
1 |
=IF(AND(C3>0,C4>0),TRUE,FALSE) |
Another example: If you wan to know if at least one of the two values is greater than 0, you use the OR formula:
|
1 |
=IF(OR(C3>0,C4>0),TRUE,FALSE) |
The last possible method of combining two conditions: The exclusive or, called XOR. Let’s say we want to know if only one if the two cells is greater than 0:
|
1 |
=IF(XOR(C3>0,C4>0),TRUE,FALSE) |
In conclusion, we got the following table:
| Option | Formula | Example |
| Both values are true | AND() | AND(C3>0,C4>0) |
| At least one of the values is true | OR() | OR(C3>0,C4>0) |
| Exactly one of the values is true | XOR() | XOR(C3>0,C4>0) |
Extend the IF formula 2: Nested IF formula
Example: Fill out cells C8 to C13
Let’s take a look at a more complex example. On the right hand side we got a picture with two tables:
- The upper table shows categories for classifying cars as cheap, medium or expensive.
- The lower table contains data. Each data set has a car type (VW Golf or Audi A3) and a price.
We want to fill in the price category in cells C8 to C13. How do we proceed using the IF formula?
Solution: Using so-called nested IF formulas. We have an IF formula within an IF formula. There are two steps:
- The normal IF formula with all its parts (check, true and false).
- Within the TRUE or FALSE part, you got another IF formula. The structure could look something like the image below.
Structure of a nested IF formula
So in theory, we set up the IF formula with the following structure:
- We check first, if we got a VW Golf or a Audi A3 (as number 1 in the image above).
- The true part (number 2): If we got a VW Golf, we check if the price is less than 20,000 (number 4). If yes, we return the value from cell B3 (“Medium”) (number 5). If not, we return the value from cell C3 (“Expensive”) (number 6).
- The false part (number 3): If we don’t have a VW Golf, we have to do the same check as before: If the price is less than 20,000, we return the value from cell B4. If not, we return the value from cell C4.
The complete formula looks like this:
|
1 |
=IF(A8=$A$3,IF(B8<20000,$B$3,$C$3),IF(B8<20000,$B$4,$C$4)) |
Please note: If there are more categories to be classified you should take a look at the SUMIFS formula.
Related formulas: IFS, IFERROR and IFNA
Besides the normal IF formula, there are three other versions in Excel: IFS, IFERROR and IFNA. These functions serve different purposes and are not available in each version of Excel. Let’s take a closer look at them and eventually compare them.
IFS formula: The alternative to nested IF functions
IFS is available since Excel 2016. So all the older versions of Excel return a #NAME? error if you try to use it then. IFS is made for replacing complex nested IF formulas. Microsoft says about the IFS function:
The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
You can have up to 127 checks or conditions. It follows a simple pattern: Check for something and get a return value. These two parts can repeat up to 127 times. Maybe the formula is easier to understand with a simple example:
|
1 |
=IFS(A1=1,"The result is 1",A1=2,"The result is 2",A1=3,"The result is 3") |
This formula checks if A1 equals 1. If yes, it returns “The result is 1”. If A1 doesn’t equal to 1, it proceeds with the 2nd check: Excel checks, if A1 = 2. If true, it’ll return “The result is 2”. If false, the next condition will be checked and so on…
Please note, that the longer this formula gets, the more difficult it is to understand and maintain. Often there are simpler solutions…
IFERROR for checking for errors
The IFERROR formula is very helpful. It checks, whether a formula or value returns an error. If yes, you can define what to do. We’ve created a comprehensive article with everything you have to know about the IFERROR formula. Please take a look at it for more information.
IFNA for checking for #N/A errors
Quite similar to IFERROR: If a formula or value returns a #N/A error, you can define what to do. The only difference to IFERROR: IFNA only checks for #N/A errors. If the result is any other value (for example a #REF! error, Excel won’t do anything.
An example: You want to get a value from table A with the VLOOKUP formula. If the value is not within table A, you want to look for it in table B.
|
1 |
=IFNA(VLOOKUP(...),VLOOKUP(...)) |
IF, IFS, IFERROR and IFNA: Comparison
The big question: Which formula to use in what case? Please take a look at the table below (the following numbers are corresponding to the table):
Comparison of IF, IFS, IFERROR and IFNA in Excel
- The first crucial condition: Which of the IF formulas is available in your version of Excel? IF is basically available in all newer versions (at least later than Excel 2003), whereas the other formulas have some restrictions here.
- If any of the formulas is not available, you can still replace it with another IF formula. For example if you want to use IFS with Excel 2013, you probably have to fall back to the nested IF option.
- All the IF formulas can check for errors, but especially IFERROR and IFNA are made for error checking.
- Furthermore, IFNA can only check for the availability of some data.
- The application is probably most difficult with the IF formula. But once you’ve understood the basic IF structure, all the other versions should be easy to use for you.
- IF and IFS are highly flexible. IFERROR and IFNA can only be used for error checking.
