

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 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:
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
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 complete formula is:
=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”.
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:
=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:
=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:
=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) |
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:
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:
Structure of a nested IF formula
So in theory, we set up the IF formula with the following structure:
The complete formula looks like this:
=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.
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 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:
=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…
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.
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.
=IFNA(VLOOKUP(...),VLOOKUP(...))
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
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.