*The tutorial explains how to use the nested IF function in Excel to check multiple conditions. You will also learn a few other functions that could be good alternatives to using a nested formula in Excel.*

How do you usually implement a decision-making logic in your Excel worksheets? In most cases, you'd use an IF formula to test your condition and return one value if the condition is met, another value if the condition is not met. To evaluate more than one condition and return different values depending on the results, you nest multiple IFs inside each other.

Though very popular, the nested IF statement is not the only way to check multiple conditions in Excel. In this tutorial, you will find a handful of alternatives that are definitely worth exploring.

Here's the classic Excel nested IF formula in a generic form:

IF(*condition1*, *result1*, IF(*condition2*, *result2*, IF(*condition3*, *result3*, *result4*)))

You can see that each subsequent IF function is embedded into the *value_if_false* argument of the previous function. Each IF function is enclosed in its own set of parentheses, but all the closing parentheses are at the end of the formula.

Our generic nested IF formula evaluates 3 conditions, and returns 4 different results (result 4 is returned if none of the conditions is TRUE). Translated into a human language, this nested IF statement tells Excel to do the following:

Test *condition1*, if TRUE - return *result1*, if FALSE -

test*condition2*, if TRUE - return r*esult2*, if FALSE -

test*condition3*, if TRUE - return *result3*, if FALSE -

return*result4*

test

test

return

As an example, let's find out commissions for a number of sellers based on the amount of sales they've made:

Commission | Sales |

3% | $1 - $50 |

5% | $51 - $100 |

7% | $101 - $150 |

10% | Over $150 |

In math, changing the order of addends does not change the sum. In Excel, changing the order of IF functions changes the result. Why? Because a nested IF formula returns a value corresponding to the **first TRUE condition**. Therefore, in your nested IF statements, it's very important to arrange the conditions in the right direction - high to low or low to high. In our case, we check the "highest" condition first, then the "second highest", and so on:

`=IF(B2>=150, 10%, IF(B2>=101, 7%, IF(B2>=51, 5%, IF(B2>=1, 3%, ""))))`

If we arranged the conditions in the reverse order, from bottom up, the results would be all wrong because our formula would stop after the first logical test for any value greater than 1. Let's say, we have $100 in sales - it is greater than 1, so the formula would not check other conditions and return 3% as the result.

As you see, it takes quite a lot of thought to build the logic of a nested IF statement correctly all the way to the end. And although Microsoft Excel allows nesting up to 64 IF functions in one formula, it is not something you'd really want to do in your worksheets. So, if you (or someone else) are staring at your Excel nested IF formula trying to figure out what it actually does, it's time to reconsider your strategy and probably choose another tool in your arsenal.

For more information, please see Excel nested IF statement.

In case you need to evaluate a few sets of different conditions, you can express those conditions using OR as well as AND function, nest the functions inside IF statements, and then nest the IF statements into each other.

By using the OR function you can check two or more different conditions in the logical test of each IF function and return TRUE if any (at least one) of the OR arguments evaluates to TRUE. To see how it actually works, please consider the following example.

Supposing, you have two columns of sales, say January sales in column B and February sales in column C. You wish to check the numbers in both columns and calculate the commission based on a higher number. In other words, you build a formula with the following logic: if either Jan or Feb sales are greater than $150, the seller gets 10% commission, if Jan or Feb sales are greater than $100, the seller gets 7% commission, and so on.

To have it done, write a few OF statements like OR(B2>=150, C2>=150) and nest them into the logical tests of the IF functions discussed above. As the result, you get this formula:

`=IF(OR(B2>=150, C2>=150), 10%, IF(OR(B2>=101, C2>=101),7%, IF(OR(B2>=51, C2>=51), 5%, IF(OR(B2>=11, C2>=1), 3%, ""))))`

And have the commission assigned based on the higher sales amount:

For more formula examples, please see Excel IF OR statement.

If your logical tests include multiple conditions, and all of those conditions should evaluate to TRUE, express them by using the AND function.

For example, to assign the commissions based on a lower number of sales, take the above formula and replace OR with AND statements. To put it differently, you tell Excel to return 10% only if Jan and Feb sales are greater than $150, 7% if Jan and Feb sales are greater than $100, and so on.

`=IF(AND(B2>=150, C2>=150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=11, C2>=1), 3%, ""))))`

As the result, our nested IF formula calculates the commission based on the lower number in columns B and C. If either column is empty, there is no commission at all because none of the AND conditions is met:

If you'd like to return 0% instead of blank cells, replace an empty string (''") in the last argument with 0%:

`=IF(AND(B2>=150, C2>=150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=11, C2>=1), 3%, 0%))))`

More information can be found here: Excel IF with multiple AND/OR conditions.

When you are dealing with "scales", i.e. continuous ranges of numerical values that together cover the entire range, in most cases you can use the VLOOKUP function instead of nested IFs.

For starters, make a reference table like shown in the screenshot below. And then, build a Vlookup formula with **approximate match**, i.e. with the *range_lookup* argument set to TRUE.

Assuming the lookup value is in B2 and the reference table is F2:G5, the formula goes as follows:

`=VLOOKUP(B2,$F$2:$G$5,2,TRUE)`

Please notice that we fix the *table_array* with absolute references ($F$2:$G$5) for the formula to copy correctly to other cells:

By setting the last argument of your Vlookup formula to TRUE, you tell Excel to search for the **closest match** - if an exact match is not found, return the next largest value that is smaller than the lookup value. As the result, your formula will match not only the exact values in the lookup table, but also any values that fall in between.

For example, the lookup value in B3 is $95. This number does not exist in the lookup table, and Vlookup with exact match would return an #N/A error in this case. But Vlookup with approximate match continue searching until it finds the nearest value that is less than the lookup value (which is $50 in our example) and returns a value from the second column in the same row (which is 5%).

But what if the lookup value is less than the smallest number in the lookup table or the lookup cell is empty? In this case, a Vlookup formula will return the #N/A error. If it's not what you actually want, nest VLOOKUP inside IFERROR and supply the value to output when the lookup value is not found. For example:

`=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), "Outside range")`

For more information, please see Exact match VLOOKUP vs. approximate match VLOOKUP.

In Excel 2016 and later versions, Microsoft introduced a special function to evaluate multiple conditions - the IFS function.

An IFS formula can handle up to 127 *logical_test*/*value_if_true* pairs, and the first logical test that evaluates to TRUE "wins":

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]...)

In accordance with the above syntax, our nested IF formula can be reconstructed in this way:

`=IFS(B2>=150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%)`

Please pay attention that the IFS function returns the #N/A error if none of the specified conditions is met. To avoid this, you can add one more *logical_test*/*value_if_true* to the end of your formula that will return 0 or empty string ("") or whatever value you want if none of the previous logical tests is TRUE:

`=IFS(B2>=150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%, TRUE, "")`

As the result, our formula will return an empty string (blank cell) instead of the #N/A error if a corresponding cell in column B is empty or contains text or negative number.

For more information, please see Excel IFS function instead of nested IF.

Another way to test multiple conditions within a single formula in Excel is using the CHOOSE function, which is designed to return a value from the list based on a position of that value.

Applied to our sample dataset, the formula takes the following shape:

`=CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>=150), 3%, 5%, 7%, 10%)`

In the first argument (*index_num*), you evaluate all the conditions and add up the results. Given that TRUE equates to 1 and FALSE to 0, this way you calculate the position of the value to return.

For example, the value in B2 is $150. For this value all 4 conditions are TRUE, meaning *index_num *equals to 4, meaning the 4^{th} value is returned, which is 10%.

`=IFERROR(CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>=150), 3%, 5%, 7%, 10%), "")`

For more information, please see Excel CHOOSE function with formula examples.

In situations when you are dealing with a fixed set of predefined values, not scales, the SWITCH function can be a compact alternative to complex nested IF statements:

SWITCH(expression, value1, result1, value2, result2, …, [default])

The SWITCH function evaluates *expression* against a list of *values* and returns the *result* corresponding to the first found match.

In case, you'd like to calculate the commission based on the following grades, rather than sales amounts, you could use this compact version of nested IF formula in Excel:

`=SWITCH(C2, "A", 10%, "B", 7%, "C", 5%, "D", 3%, "")`

Or, you can make a reference table like shown in the screenshot below and use cell references instead of hardcoded values:

`=SWITCH(C2, $F$2, $G$2, $F$3, $G$3, $F$4, $G$4, $F$5, $G$5, "")`

Please notice that we lock all references except the first one with the $ sign to prevent them from changing when copying the formula to other cells:

**Note**. The SWITCH function is only available in Excel 2016 and higher.

For more information, please see SWITCH function - the compact form of nested IF statement.

As mentioned in the previous example, the SWITCH function was introduced only in Excel 2016. To handle similar tasks in older Excel versions, you can combine two or more IF statements by using the Concatenate operator (&) or the CONCATENATE function.

For example:

`=(IF(C2="a", 10%, "") & IF(C2="b", 7%, "") & IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1`

Or

`=CONCATENATE(IF(C2="a", 10%, ""), IF(C2="b", 7%, ""), IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1`

As you may have noticed, we multiply the result by 1 in both formulas. It is done to convert a string returned by the Concatenate formula to a number. If your expected output is text, then the multiplication operation is not needed.

For more information, please see CONCATENATE function in Excel.

You can see that Microsoft Excel provides a handful of good alternatives to nested IF formulas, and hopefully this tutorial has given you some clues on how to leverage them in your worksheets. To have a closer look at the examples discussed in this tutorial, you are welcome to download our sample workbook to Excel Nested If Statements. I thank you for reading and hope to see you on our blog next week!

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 27 Responses to "Excel Nested IF statement: examples, best practices and alternatives"

Dear Sir,

Kindly confirm one coloum are value 1-15, and secound Coloum value 15-30, if kindly confirm which sort out the value's of Below & uper.

& kindly confirm vlookup formulas fungtions.

I have the following data in C7:

S123 - using formula will result in D7 as 80123

SA123 - using formula will result in D7 as 81123

E123 - using formula will result in D7 as 82123

EA123 - using formula will result in D7 as 83123

C123 - using formula will result in D7 as 84123

CA123 - using formula will result in D7 as 85123

U123 - using formula will result in D7 as 87123

UA123 - using formula will result in D7 as 86123

I tried to nest IF statements but excel is returning an error that maximum nesting is reached.

FORMULA:

=IF(LEFT(C7,2)="SA",CONCATENATE(81,RIGHT(C7,3)),IF(LEFT(C7,1)

="S",CONCATENATE(80,RIGHT(C7,3)),IF(LEFT(C7,2)="UA",CONCATENATE(87,RIGHT

(C7,3)),IF(LEFT(C7,1)="U",CONCATENATE(86,RIGHT(C7,3)),IF(LEFT(C7,2)

="CA",CONCATENATE(85,RIGHT(C7,3)),IF(LEFT(C7,1)="C",CONCATENATE(84,RIGHT

(C7,3)),"-"))))))

I'm using a nested IF formula for conditional formatting and to evaluate if the date in a cell is equal to today =IF(I2=TODAY(),I2,IF(J2="NDA",J2,IF(J2="SDA",J2))). I want the formula to stop if the date in cell I2 is not equal today. The formula should stop at the first false argument however the formula evaluates all the arguments and returns a NDA which is the value in J2. I have evaluated the formula using Formula Auditing and I get a false value whether or not my first logical test is nested in an nested IF formula.

I have a summary of invoices (positive) and credit notes (negative).

When I knock off against payment made it shows zero.

I am trying to make an if statement which would show:-

If zero - "-"

If more than .01 - "OS" (invoice)

If more than -.01 - "OS" (CN)

The aim is to filter what are the unpaid invoice and CN to generate

a payment proposal for those outstanding.

Thanks

IF sentens:

About getting % when buying

Ih I by <= 4 pallets I´ll get 60%

If I by 5 but les than 9 pallets I'll get 60% and from his price I get ekstra 5 %

J=Amount of Pallets

L= My price (pallets times price pr. pallet)

M= 60 %

This works perfectly:

=IF(J6<=4;L6;L6-(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,95)

My problem comes when I add this:

10 pallets but than 15 pallets I get 10 % (so first the 60 % and than an ekstra 10 after the first results.

Like this:

=IF(J6<=4;L6;L6-(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,95);(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,93);(IF(OR(J6=5;J6<=9);L6*(M6))))*(0,90)

if D4<=4,

true E4*50%,

False E4*100% but if

E45%, true E4*100%,false 5%

I'm having trouble with this formula. I have three conditions and they need to all be true to return "TRUE". If any are not true, it should return "FALSE". Here's what I've tried now, which does not work.

=IFS(C:C="CSD_SERVICES", "TRUE", (K:K=0,"TRUE", (O:O="","TRUE","FALSE")))

I've tried probably 25 different iterations but so far none returns the correct answer.

Hello. I have look different ways, but I'm running out of time to complete a project I'm working on. I have a table like this, is a changing pattern, so there is not actually a pattern, but there are some blank cells in between the text in Column A. In Column be I have tax names, which is just next. I need to be able to concatenate a formula with if or I don;t know to be able to concatenate tax names on the row with out blanks, I need to delete the blanks but have that information in every text

A B C

Text1 tax1 tax1,tax2

blank tax2 ------------

text2 tax1 tax1,tax2

blank tax2 --------------

text3 tax1 tax1

text4 tax1 tax1

text5 tax1 tax1

text6 tax1 tax1,tax2,tax3,tax4,tax5

blank tax2 --------

blank tax3 --------

blank tax4 ---------

blank tax5 -------

text7 tax1 tax1,tax2,tax3,tax4

blank tax2 ------------

blank tax3 ------------

blank tax4 -------------

text8 tax1 tax1

text9 tax2 tax2

text10 tax3 tax3,tax2

blank Tax2 ---------

test11 tax1 tax1

My formula wont work, can anyone see where I am going wrong?

=IF('UTILITY Rates'!A2:A13,'Utility Score Card'!C1,"'UTILITY Rates'!C2")

Rustin.

Your formula is missing the condition. If 'UTILITY Rates'!A2:A13 equal, larger, contains, etc

I'm trying to make a formula along the lines of if D3>= 2300 multiply by J3, once 2300 is exceed the difference should be multiplied by K3. Is this possible?

I have 2 work sheets, the first one named Property 1 and the 2nd one named summery.

In the first sheet I have expenses listed by date, amount and category. the categories are Repairs,Labor,Materials,Transport,Advertising and Commission.

I would like to sum each category monthly (Jan,Feb.....) and display the total on the Summery sheet.

Can I use nested IF to do the following :

I have a column with three possibilities entered in the cells : ABC DEF GHI

the next column in the contagious cell needs a number based on the above so that :

ABC would be 123

DEF would be 456

GHI would be 789

Not working. Please help please. I need to use 16 conditions for if statement but as you see, it is only 10 and not working. It said that "this formula uses more levels of nesting than you can use in the current file format". Anyone please.

Thanks!

=IF(S10>=95,20, IF(S10=94,19, if(s10=93,18, if(s10=92,17, if(s10=91,16, if(s10=90,15, if(s10=89,14, if(s10=88,13, if(s10=87,12, if(s10=86,11))))))))))

I am attempting to create a dynamic table where the value in one cell makes another equal a 3rd cell plus the data in another cell. So for example, =IF(F2=P7,G2+Q7,"-") I can get it to work with that one---but I need to nest that statement with 10+ others of the same type (=IF(f2=P8,G2+Q8) etc etc (pulling data from a table). I'm lost.

I am attempting to create a formula with IF statements.

Here it is:

G6 =TODAY() which inputs today's date.

J6 If "Yes" is selected

L6 will input TODAY()+7

=IF(J6="Yes",G6=TODAY()+7) the result will go into L6

Thank you

I have a formula I am trying to use for Overbillings and underbillings, each a separate column. If I use one column the formula looks like this:

=IF([@[% Comp]]<30%,[@[Earned Cost]],[@[% Comp]]*[@[Contract Amount]])-[@Billed]

but I want it to give the result of zero if the answer to this in the overbillings is over zero. What do i add?

Error in your explanation of the SWITCH function. Line 1, you use SWIFT. I expect you mean SWITCH. #yourewelcome

Of course, I meant SWITCH. Fixed, thank you!

I need help in defining the reorder level using "IF" or any other formula in excel

Column A = Shortage = 18500

Column B = MOQ = 5000

Column C = Reorder level = ?

I would like to calculate the reorder level as follows :-

(ie. Reorder level should be = to MOQ if shortage is less than MOQ

OR Reorder level should be 20000 if shortage is between 15001 & 20000

ie. Reorder level should be in multiples of MOQ but > shortage

Kindly confirm how to use "IF" formula or any other formula in excel

Tony,

You do not need an IF function for this.

Try:

=CEILING(shortage, 5000)

This will round your shortage number up to the next 5000.

K

Thanks a lot. it did work

Awesome article. I switched nested ifs for Lookup - made it so much easier. Also now I can go to the lookup table and change values without having to copy paste the formula again. Thanks....

I just want to say thank you. I was able to create an if-choose on my excel thanks

I need to do an IF statement to get a range for:

Volumes Greater than and equal to 2,000,000

Volumes Less than 2,000,000 but greater than and equal to 200,000

Volumes less than 200,000 but greater than and equal to 500

Volumes less than and equal to 500

Hello please could you wizards advise on the following:

=IF(G40="","",IF(J40=G40,"Contact individual",IF(M40="","",IF(J40>=M40,"Returned","Contact individual")))))

I require a nest IF (if think)

I have three dates and three different document status outcomes depending on the sequence below.

If the issue date is is filled in only, then the status shall be "in circulation".

if there is now a document withdrawal date in the next cell that the is greater or equal to the issue date, the value shall be "Contact individual".

If the return date value is missing from the required cell then the status shall remain as "Contact individual", but if a date is entered into the withdrawal date cell the status then the status is to return "Returned"

Please Help Friends

FYI =IF(G39="","",IF(J39=G39,"Contact individual",IF(M39="","",IF(M39<=J39,"Returned","Contact individual")))))