by 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.
Excel nested IF statement
Here's the classic Excel nested IF formula in a generic form:
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 condition2, if TRUE - return result2, if FALSE -
test condition3, if TRUE - return result3, if FALSE -
return result4
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, depending on your formula's logic. 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 placed the conditions in the reverse order, from the bottom up, the results would be all wrong because our formula would stop after the first logical test (B2>=1) 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.
If you'd rather arrange the conditions from low to high, then use the "less than" operator and evaluate the "lowest" condition first, then the "second lowest", and so on:
=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))
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 gazing 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.
Nested IF with OR/AND conditions
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.
Nested IF in Excel with OR statements
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 or equal to $101, 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>=1, C2>=1), 3%, ""))))
And have the commission assigned based on the higher sales amount:
For more formula examples, please see Excel IF OR statement.
Nested IF in Excel with AND statements
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 or equal to $101, 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>=1, 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>=1, C2>=1), 3%, 0%))))
More information can be found here: Excel IF with multiple AND/OR conditions.
VLOOKUP instead of nested IF in Excel
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 continues 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")
Important note! For a Vlookup formula with approximate match to work correctly, the first column in the lookup table must be sorted in ascending order, from smallest to largest.
For more information, please see Exact match VLOOKUP vs. approximate match VLOOKUP.
IFS statement as alternative to nested IF function
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":
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.
Note. Like nested IF, Excel's IFS function returns a value corresponding to the first condition that evaluates to TRUE, which is why the order of logical tests in an IFS formula matters.
For more information, please see Excel IFS function instead of nested IF.
CHOOSE instead of nested IF formula in Excel
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, the first 3 conditions are TRUE and the last one (B2 > 150) is FALSE. So, index_num equals to 3, meaning the 3rd value is returned, which is 7%.
Tip. If none of the logical tests is TRUE, index_num is equal to 0, and the formula returns the #VALUE! error. An easy fix is wrapping CHOOSE in the IFERROR function like this:
=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.
SWITCH function as a concise form of nested IF in Excel
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:
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.
Concatenating multiple IF functions in Excel
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 below. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel nested If statement - examples (.xlsx file)
230 comments
i am trying to add to the if(and function to display information from other cells after it is through as shown below
column info
A-their first name
B-their last name
D their sex "M" or "F"
E- client or staff
=IF(E3="staff", "Dr.", IF(AND(E3="client", D3="M"), "Mr.", IF(AND(E3="client", D3="F"), "Ms.",)))
I am trying to get the above formula to display (Dr./Mr./Ms.) (First Name from column A) (Last Name from column B) in one cell "Dr. Gregory House"
Hello!
Please try the following formula:
=IF(E3="staff","Dr.",IF(AND(E3="client",D3="M"),"Mr.",IF(AND(E3="client",D3="F"),"Ms.",)))&A3&" "&B3
Hope this is what you need.
Hello,
I am attempting and if and or statement, but having trouble getting to the correct result. part I
If(AJ11="US","TRUE",IF(AJ11="",IF(AG11="US","TRUE",IF(AG11="",IF(AI11="US","TRUE","review"))))), returning false instead of True or review.
there are 3 col with data points, if no US or blank = true, if any us, blank and non us, test
if result is to test, the 2 part is to test for 2 letter code to either test or not test.
suggestions?
Hi!
Unfortunately, your explanations are incomprehensible. Describe the problem more clearly.
Hi there,
I am struggling with my formula I'd love some help:
I have 8 data values validated in cell I2, and will be entering a manual number in cell J2 and believe I need an IF formula in column K to easily calculate the following scenario.
If the frequency = weekly (cell I2), and the number of hours =x, then the monthly hours when annualised would be (J2*52)/12).
The other values in I2 are:
Fortnightly
2 x Weekly (needs annualising then multiplying by 2)
3 x Weekly (needs annualising then multiplying by 3)
4 x Weekly (needs annualising then multiplying by 4)
5 x Weekly (needs annualising then multiplying by 5)
6 x Weekly (needs annualising then multiplying by 6)
7 x Weekly (needs annualising then multiplying by 7)
One-Off (does not need annualising)
The current formula I have is:
=IF(I2="Weekly",((J2*52)/12),IF(I2="Fortnightly",((J2*26)/12),IF(I2="3 x Weekly",((J2*52)/12)*3),IF(I2="2 x Weekly",((J2*52)/12)*2""""))))
It worked until I put the last IF combination in
I'd really appreciate some help. Thank-you :-)
Hello!
Please check the formula below, it should work for you:
=IF(I2="Weekly",J2*52/12,IF(I2="Fortnightly",J2*26/12,IF(I2="3 x Weekly",J2*52/12*3,IF(I2="2 x Weekly",J2*52/12*2,""))))
That worked. Thanks very much!
Hi!
Needed your expertise to correct my formula:
Column A: Interval: 1,2,3,4,5,6,7,8,9,10
Column B: P/MP/OP: OP,OP,OP,OP,MP,MP,MP,P,P,P
Column C: Dispatch: 1.29,0.45,1.29,1.24,0.26,1.51,1.31,0.14,1.25,0.25
Column D2: 1.30
Formula: =IF((OR(A2="MP,A2="OP")),AND(IF(C2>=0.5,$D$2,C2)))
The result must be: if A2 is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If A2 is P and C2 is lower or higher than 0.5, it will give me 0 answer. I only needed to capture the value of MP and OP.
Thank you.
Hello!
Your task is not completely clear to me.
The condition "C2 is lower or higher than 0.5" does not make sense as it is any number. Action is not specified if no condition is met. There is a reference to D2 in the formula. Your description doesn't say anything about D2.
Clarify.
Column D2: Firm Dispatch: 1.30 (any value in column C at a minimum requirement dispatch criteria of 0.5, it will still deliver(choose) 1.30 firm. If column C dispatch is lower than 0.5 dispatch criteria, it will choose the value of that cell in column C. Another criteria is for MP and OP only, if B2 is "P" = 0
Column F2: My initial formula: =IF((OR(B2="MP",B2="OP")),AND(C2>0.5,$D$2,C2)))
Sorry for the confusion, I am referring to "B2" and not "A2":
The result must be: if B2 (not A2) is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If B2 is P, result must be 0. I only needed to capture the value of MP and OP.
Hope this helps.
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(OR(B2="OP",B2="MP"), IF(C2>=0.5,1.3,C2),IF(B2="P",0,C2))
I have achieved the result I have been looking for. Thank you very much!
Hello,
=IF(D5>75,16,IF(D5>70,15,IF(D5>65,14,IF(D5>60,13,IF(D5>55,12,IF(D5>50,11,IF(D5>45,10,IF(D5>40,9,IF(D5>35,8,IF(D5>30,7,IF(D5>25,6,IF(D5>20,5,IF(D5>15,4,IF(D5>10,3,IF(D5>5,2,IF(D5<=5,1))))))))))))))))
Using above formula to show 1 manager for every 5 employees - (ie. 6-10 employees requires 2 managers, 11-15 employees would require 3 managers, 16-20 employees would require 4 managers)
Is there a more efficient/condensed way to accomplish this?
Hello!
You can use this formula:
=ROUNDUP(A2/5,0)
You can learn more about ROUNDUP function in Excel in this article on our blog.
"IF(R10>1,Q10*5,IF(R10<0,Q10*7))" Is tje formula is right or contains any error, because i am getting only Q*5 result where it should be Q*7,
Hi!
I don't know what values you are using. If R10 contains -10, then the formula returns Q10*7
Need help to have text & number sequencing based on a drop down list value (to confirm document type and number)
Column C lists document type from a 3-choice drop down list "Policy","Standard", or "Other"
Columns E: G contain sequential values for each of the document types.
Column E "Policy" list of sequential values 0001-PL (continues as 0002-PL, etc. starting in row 2)
Column F "Standard" list of sequential values 0001-ST (continues as 0002-ST, etc. starting in row 2)
Column G "Other" list of sequential values 0001-OT (continues as 0002-OT, etc. starting in row 2)
Since Column C would have a list that is not sequential, how can I ensure column D sequences document number correctly?
User enters in Column C Row 2 "Policy", Column D will populate as "0001-PL"
User enters Column C Row 3 "Standard", Column D populates as "0001-ST"
User enters Column C Row 4 "Policy", Column D populates as "0002-PL"
How can I make sure that when someone enters one of the three document types, column D picks up last sequence of "xxx-PL"?
Hello!
Write the formula in cell D2:
=TEXT(COUNTIF($C$1:C2,C2),"0000")&"-"&C2
After that you can copy this formula down along the column.
Please have a look at this article: How to count cells with certain text
You can learn more about TEXT function in Excel in this article on our blog.
I hope I answered your question.
Hi
I have doubt
Please help to formula below:
If A1 "-", B1 "-", answer "ok"
If A1 "", B1 "", answer "ok"
If A1 "", B1 "-", answer "check"
Thanks
Please I have a question that I need to solve in nested if c ++
How do I combine =IFERROR(AVERAGE(E6:E8),"") with =AVERAGEIF(E6:E8,"0")
Hi,
The formula AVERAGEIF (E6: E8, ”0 ″) and AVERAGEIF (E6: E8,” ″) means that you are calculating the average over blank and text cells. It doesn't make sense as it will result in an error. Explain what you want to calculate.
SOMEONE PLEASE HELP:
So I am working on jasperactive, one of the projects has me come up with a function that will display Two different texts and if it doesn't match either, it will display a blank
-the original function is: IF(AND(G2>F2,F2>E2),"Growing energy source","")
-Then it asks for you to add in a function that will display "Shrinking energy source" is G2<F2 and F2<E2
-i have been trying to make a nested formula for it that will work for hours with no luck. if anyone can help that would be amazing
Hi,
I hope you have studied the recommendations in the tutorial above. It contains answers to your question
=IF(AND(G2 > F2,F2 > E2),"Growing energy source", IF(AND(G2 < F2,F2 < E2),"Shrinking energy source",""))
=IF(G17>1.63,12CFW, IF(G17>1.3,10CFW,IF(G17>0.978,8CFW,IF(G17>0,6CFW,""))))
excel says there is a problem with this formula? help would be much appreciated, thank you.
Hi,
Text values in formulas must be enclosed in quotation marks.
=IF(G17>1.63,"12CFW", IF(G17>1.3,"10CFW",IF(G17>0.978,"8CFW",IF(G17>0,"6CFW",""))))
I hope it’ll be helpful.
demurrage charges (free time = 3 days) 1-4/1/2021) free time
demurrage charges = 18 days
4-6 days = usd25
7-9 days = usd40
10-12days = usd60
Thereafter = usd75
what is if?
Hello!
Your task is not completely clear to me.
Explain: 4-6 days=usd25. 25 - is it in one day or all the time?
If cell A1 =family and cell A2=1 A3 =A7
if cell A1 =family and cell A2=2 A3 =A8
if cell A1 =family and cell A2=3 A3 =A9
if cell A1 =Single and cell A2=1 A3 =B7
if cell A1 =Single and cell A2=2 A3 =B8
if cell A1 =Single and cell A2=3 A3 =B9
Could someone help me to figure out one If formula that involve multiple conditions.
For example, I have 7 different project code name in G2, and G3 is associated project name.
and when I clicked project code in G2, and G3 will auto pop out the right project name.
I know how to compare within two code. but not sure how to do with multiple different ones.
Below is an example that i made it up for comparing two projects.
=IF(TASK_CODE="123456-789","Bill","Cheques")
Thank you so much
Hello!
I recommend using the VLOOKUP function to select the desired value.
Please check out the following article on our blog, it’ll be sure to help you with your task: How to do Vlookup in Excel
I hope I answered your question. If something is still unclear, please feel free to ask.
Hello- I am trying put a formula together for the below conditions
For Example
IF C2=7.5 Then it should RANK 5 similarly IF C2=8.8 Then it should RANK 4
Below Scale for Each RANK
>11 = RANK 1
<10 - 9- 8-<=9 = RANK 4
<8 = RANK 5
Please help.
Hello!
Please check out the following article on our blog, it’ll be sure to help you with your task — how to use multiple conditions in a IF function.
I hope it’ll be helpful.
Hi, I'm hoping that I'm not too far off the mark in this area :-).
I have a register of risks where I want to flag as overdue. A critical must be attended to within 7 days, High 14, Med 30 and Low 60. My stab at it as below fails with an error. Any ideas please?
X and F contain the age and severity data.
=
IF(AND(X10="Critical",F10<7),OK,
IF(AND(X10="High",F10<14),OK,
IF(AND(X10="Medium",F1060),OK,
Overdue))))
Hello!
If I got you right, the formula below will help you with your task:
=IF(AND(X10="Critical",F10<7),"OK", IF(AND(X10="High",F10<14),"OK", IF(AND(X10="Medium",F10<60),"OK", "Overdue")))
I hope my advice will help you solve your task.
I have three options...3 or less yes ...less committed, 4 yes...average commited. More than 4 yes... hifhly committed for E5 to K5...please help
Hello!
The description of your conditions is not very clear. Please reread the article above, it covers your case completely. Thank you.
i am looking fro a formula for below problem.
We have 3 cells a1,b1&c1.
in cells d1-->if out of 3 cells only single cell contain value >0 then it will show Ok otherwise not ok.
FOr EX:
a b c d
0 0 0 OK
1 0 0 OK
1 1 0 not ok
0 1 0 ok
Hello!
The formula below will do the trick for you:
=IF(COUNTIF(A1:C1,">0")=1,"OK","Not OK")
Hope this is what you need.
=IF(P7="A",IF(Q715000,Q750000,Q7*60%/30*O7,0)))),IF(P7="B",IF(Q715000,Q750000,Q7*60%/30*O7,0))))
the above formula not run please give the solution to me