## Nested IF functions

*Article last updated on April 02, 2018*

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.

Use VLOOKUP to calculate discount percentages

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

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

If cell equals value from list

The array formula in cell C3 checks if cell B3 is equal to any of the values in E3:E5, the […]

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), […]

The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]

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 […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.