Nested IF functions
Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are more or less complicated to read and thankfully there are better alternatives.
You are allowed to use up to 254 nested IF statements but before you do that make sure you read this article, I promise you it will save you a lot of time.
This article will provide step by step tutorials for your specific scenario. If you can't find what you are looking for here please comment and I will add the missing part.
Nested IF functions between two numbers
You don't need to use multiple IF statements if you want to check that cell value is between two values, it is enough to simply use two logical expressions in the first argument.
The following formula returns TRUE if the value in cell B3 is equal to or greater than 0 and is equal to or smaller than 10.
Each logical expression is encapsulated with parentheses, this makes sure that the comparisons are made before multiplying the expressions. In other words, the parentheses determine the order of calculation.
The asterisk between the logical expressions means that both logical expressions must return TRUE for the logical test argument to return TRUE.
Nested IF statements with ranges
The picture above shows you nested IF statements that allow you to return different outcomes depending on the value in column B.
A value equal to or greater than 0 and smaller than 10 is in "Group 1".
A value equal to or greater than 10 and smaller than 20 returns "Group 2".
Imagine that you have 5 different groups, you now need to add 3 more nested IF statements. The formula grows considerably, however, there is a smart alternative to nested IF statements.
The VLOOKUP lets you easily group numbers using a simple function instead of constructing a megaformula that is hard to follow and troubleshoot.
Need more groups? No, problem. Add groups to the first table and then adjust the cell reference in the second argument in the VLOOKUP function.
Note, the first table must have the first column sorted from small to large. Make sure you use TRUE in the fourth VLOOKUP argument. This means that it only needs an approximate match.
Read more about the VLOOKUP function.
Nested IF functions with text
These nested IF statements check if a value is equal to "V" and returns "Level 1", if false it continues to the next IF statement and there check if the value is equal to "D". If it is it returns "Level 2"
You can use the VLOOKUP function in this case as well as an alternative to nested IF functions.
You need to find an exact match in this case so change the fourth VLOOKUP argument to FALSE.
The VLOOKUP function looks for the value in the first column (B3:C6) and returns the corresponding value in the second column. That is why I use 2 in the third VLOOKUP argument.
Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]
Nested if statements with dates
The above image demonstrates how to use multiple date ranges with a short and simple VLOOKUP function. A date range consists of two dates, since these date ranges are contiguous the end date also represents the start date for the next range.
The following picture shows how to setup non-contiguous date ranges.
The first cell range is between 1/1/2017 and 2/15/2017. The second cell range is between 3/1/2017 and 6/1/2017.
The VLOOKUP function returns 0 if a cell is empty, to prevent that type ="" and it will return a blank cell.
Download excel *.xlsx
Related articles
IF with AND function – multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]
Use IF + COUNTIF to perform numerous conditions
The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF […]
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]
If cell contains text from list
The array formula in cell C3 checks if cell B3 contains at least one of the values in List (E3:E7), […]
If cell equals value from list
Regular formula Formula in cell C3: =IF(COUNTIF($E$3:$E$5,B3),"Yes","No") The COUNTIF function counts how many values in E3:E5 match cell B3, it returns […]
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
If cell contains multiple values
The array formula in cell C3 checks if text string in B3 contains all values in F2:F3. Related articles
IF with AND function – multiple conditions
The AND function allows you to have multiple conditions in an IF function, you can have up to 254 arguments. […]
The picture above shows different values in column B and a formula in column C that tries to identifies the […]
The easiest way to check if a cell has a value is, in my opinion, to use the equal sign […]
3 Responses to “Nested IF functions”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
Another problem that is commonly solved by nestetd if is the intersection of two intervals.
Alternative formula:
IF A1 and A2 are the ends of the interval A
and B1 and B2 are the ends of the interval B
then intersection of interval A with interval B is:
=MAX(0,MIN(A2,B2)-MAX(B1,A1)+1)
Ciprian Stoian,
Thank you for your comment.
If interval A is 1 to 7 and interval B is 4 to 11 then the intersection is 4 to 7?
Ciprian Stoian
I now understand, your formula calculates if two intervals intersect or not.