How to
avoid nested IFs
in Excel
Some people insist on doing things the hard way – such as using nested IF statements in Excel to handle multi-condition behaviours. Quite simply – you hardly EVER have to use nested IF constructs… I went Googling to try and find a really good case of where a nested IF was essential. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CASE 1My first Googling found… http://www.cpearson.com/excel/nested.htm It presents the case of… Suppose we wanted an nested IF formula to test: The author then creates a massively complicated IF solution. This is an example of where you just would not use nested IF at all. You would use VLOOKUP. Since the lookup values are simply 1 to 13, just use:
Name the above range “table” and use =VLOOKUP(A4,table,2) Far better than twisting yourself up with IF… |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Another strategyThe CHOOSE() function also lets you easily look up values for an index value e.g. =CHOOSE(daynumber,”Sunday”,”Monday”,”Tuesday”… etc) That may be useful too! |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CASE 2My second Google led to… http://spreadsheets.about.com/cs/excelfunction1/a/nestediffunct.htm It says… To illustrate, let’s say you have a spreadsheet that you use to keep track of your sales force. The rate of commission each sales person receives is based on the amount of sales they have generated for that month. For example: From $1 to $10 earns 10% commission From $11 to $100 earns 15% commission Anything over $100 earns 20% commission Assuming the amount of sales is in column B, starting at row 4, and that the column containing the commission is formated for percentages, this is what the nested IF function would look like: =IF(B4<=10,”10″, if(b4<=100, “15”, “20”)) Once again, a simple VLOOKUP is far superior.
Name the range (excluding headings) “table” and then use =VLOOKUP(sales,table,3) (Assuming the value to look up is in a cell named “sales”) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
NOTENote the method I now use for setting out lookup tables. I used to get mental cramps working out the cutoff values for categories. Now I use “From” and “To” columns to make it much easier to determine the numbers. The second column is actually never used by the lookup, but it helps my brain. Life is so much more pleasant now 🙂 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CASE 3Example 3, found at http://www.techonthenet.com/excel/formulas/if_nested.htm Question: In Excel, I need to write a formula that works this way: If (cell A1) is less than 20, then times it by 1, > If it is greater than or equal to 20 but less than 50, then times it by 2 > If it is greater than or equal to 50 and less than 100, then times it by 3 And if it is great or equal to than 100, then times it by 4 > Answer: You can write a nested IF statement to handle this. For example: =IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4))) Once again, it’s far easier to use …
Name the range “table” (or whatever) In the target cell just put =A1 * VLOOKUP(A1,table,3) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CASE 4Example 4, found at http://www.techonthenet.com/excel/macros/if_custom.htm > Question: I have a formula in Excel that I am using to test for 7 conditions, and each condition if true will return a different value. However, I now need to test a total of 12 possible values. The limitation of the nested IFs is that you can only nest up to 7. Is there an alternative to this formula to test so that I can test for 12 values instead of 7? =IF(A1=”10X12″,120,IF(A1=”8×8″,64,IF(A1=”6×6″,36,IF(A1=”8×10″,80,IF(A1=”14×16″,224,IF(A1=”9×9″,81,IF(A1=”4×3″,12))))))) Answer: There is no built-in alternative formula in Excel, but you could write your own function in VBA and then call this new function > instead. Geez! You could (again) use vlookup…
Sort by the first column so they’re sorted alphabetically (or see the tip below). Call it “table” and use =VLOOKUP(a1,table,2) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
TIPIf searching a table for an exact match (e.g. cases 1 and 4) rather than for a value fitting into a continuous range (e.g. values from 26 to 73) you can use the mysterious optional parameter in vlookup… =VLOOKUP(a1,table,2, FALSE) By adding the ” ,FALSE”, vlookup will look for an exact match for the lookup value in the table. Note! It gives an error if the exact value is not found. Using the extra “,FALSE” means you don’t have to sort the lookup rows into ascending order.. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
In short: I (nearly) never use nested IFs because usually there are easier and smarter alternatives. Here’s a challenge: come up with a neat example where it is essential to use nested IFs! Tell me about it! | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
The IF Challengers |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CHALLENGE 1A worthy challenge from yangontha on 28 October 2006. If memory serves me correctly, yangontha said: Column A has a property type and the value ranges from 1 to 5. Column B and C has some decimal values. In each row, depending on the value of type, the different formula is used to calculate for column D. For example, if the type is 1, then the values in column B and C for that row are added and put in column D. If the type is 2, then the values in column B and C for that row are multiplied and put in column D. Depending on the value of type, there is a different formula. The problem is how to put the formula in column D so that the respective formulae is used to calculate for column D
I am not saying, nested if is necessary. But I can’t think of a way, how to put the formula in column D. This is what I replied to yangontha. Hi Yangontha. Not even a nested IF is required using the sample data you provided. There are only 2 operators to cope with (multiply and add) so a simple IF does the trick.
Mind you, if you added divide and subtract to get four conditions rather than two, a different approach would be needed – but not VLOOKUP in this case – you’d use the CHOOSE function (as mentioned above).
It’s not VLOOKUP, perhaps , but I never said VLOOKUP was the answer for everything. You always use the best-fitting solution to a problem: you don’t force the one tool you know into solving every problem. Good try , but nested IF is still a beaten foe. 🙂 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CHALLENGE 2‘Curious’ wrote on Saturday, December 30, 2006 at 09:51:40 Hi Mark – What about if you have a range of dates formatted in date form (i.e. 12/29/2006) and you want Excel to search that range of data and return the month in text form (i.e. DEC). Isn’t it necessary to use a nested IF for this case? I tried using a vlookup formula (quite similar to the first example listed on your website) and making a table (named MONTHS) that defines each month (ex. 1 = Jan, 2 = Feb, and so on)… naming that table and then using the following vlookup formula: =VLOOKUP(A9,MONTHS,2) I had no luck with this and am convinced that in this instance, a nested IF formula is needed. What do you think? Much appreciated, Curious And I replied: Hi curious. You were SO close! The problem you had was that you had not extracted the month from the date to use in the lookup. VLOOKUP will do the job very nicely, with the addition of one simple MONTH( ) function 🙂 Download the Excel solution The formulas: |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CHALLENGE 3On 15 August 2007, Jay wrote to say: Here’s 1, I am having trouble coming up with ANY way of solving: I have 6 columns of data. I want to be able to set up a 7th column that tells me whether I have passed or failed my test criteria (by saying PASS or FAIL). the failing criteria is if 2 or more of the 6 columns display a value less than 85. The nesting IF function can only nest 7, with 15 possible combinations in this example, so thats not even an option. Thanks! Hi Jay. What about this: =IF(COUNTIF(A3:F3,”<85″)>=2,”Fail”,”Pass”) It simply counts the number of cells that are less than 85. If there are 2 or more, they fail. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CHALLENGE 424 September 2007 Hi, Ok here we go….. I have created a MS EXCEL 2007 Workbook that got two sheets. I have attached picture of both sheets. in “Sheet 1” there is three Field 1. ID:- This is to track the customer ID=1 Means its “Customer 1″…. 2. Bill No:-This is to track Month Bill 1 Means Month=January… 3.Total Bill:- This is where the problem occurs. In sheet two there is a MATRIX TABLE…What i need to do is Bring the proper value from sheet2. Like—- if ID=1 and Bill No=1 then it will show value from B4 in sheet2. Everything is ok . i have the code which is working but within the limit. It only accept 64 Argument or nested IF else. But my requirement is more than that. The Code is Something like that =IF(AND(N13=1,N14=1),Readings!B4,IF(AND(N13=1,N14=),Readings!C4,…………….. I heard that there is a way if i make a FUNCTION and call it . But i don’t now how. if u can help… Thankx And the result is once again far easier than expected, so long as you know what Excel has in its toolbox for you to play with… Assuming: – the customer ID cell is named ‘customer’ and – the month number cell is named ‘month’ and – the table (excluding the heading row and column) is named ‘table’… the fomula for the total bill is simply: =INDEX(table,customer,month) Easy as that. You can download the solution if you like. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CHALLENGE 511 October 2007 Sara wrote to say… Hi Mark:
I’m quite intrigued on your battle against the nested ifs!
Here’s another challenge…you haven’t addressed if the condition meets 2 requirements such as:
=IF(AND(M11=”A”,J11>(TODAY()-1095)>0),”No”,”Yes”) =IF(AND(M11=”B”,J11>(TODAY()-1000)>0),”No”,”Yes”) =IF(AND(M11=”C”,J11>(TODAY()-800)>0),”No”,”Yes”) =IF(AND(M11=”D”,J11>(TODAY()-500)>0),”No”,”Yes”) =IF(AND(M11=”E”,J11>(TODAY()-200)>0),”No”,”Yes”) and so on…I’m driving myself crazy with the ifs….could you help? Your solutions and answers to challenges are greatly appreciated!!!
Take care, Sara
This was an interesting challenge, but solvable with a bit of lateral thinking and a nested LOOKUP. Entering an arbitrary “type” (A-F in this case) into C2 and a “days ago” into C2 gives the value matching that combination of days and type. The trick is to use a VLOOKUP with a HLOOKUP (its less famous cousin) inside it. The formula in F12 is =VLOOKUP(C2,E6:F10,2) + 1 This handles the first step of the two-part lookup – it looks up the “days ago” value in the table and returns a row number (that’s the tricky bit). Then, the formula for the answer is =HLOOKUP(C1,G5:L10,F12) This looks up the “type” in a HLOOKUP and uses the previous VLOOKUP’s value to act as the HLOOKUP’s row parameter. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Challenge 6 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
I just found this one lurking in the bottom of my hard disk. I must have lost it or forgotten to upload it. Anyway, it’s an interesting one. Someone wanted to know how to work out salaries that depended on not one but two variables: length of service and education level without the use of tortuous nested IFs. In effect, they wanted to get this… To solve this required a bit of lateral thinking – instead of a nested IF, we need a nested lookup! First, we do a normal lookup of years of service to find the right row in the salary table. So James, for example, with 6 years of service is looked up in the B5:E9 table and we find he’s in row 2 of the table. The hard bit is to then determine which column we need to read from to get his salary; it depends on the degree he holds. This is where the second lookup happens. The “column” parameter of the first lookup will not be a constant – it will be dynamically calculated by another lookup. The second lookup looks up the person’s degree in a second table (G6:H8). It returns a number from 2 to 4, which is used to select a column value for the first lookup. The result is something like this: Note the extra fourth parameter in the nested lookup – it allows the degrees to be in any order rather than sorted alphabetically. Download the Excel source. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
CHALLENGE 7Loti wrote in 2008… I am an HR Officer responsible for recruiting employees for a Mining company that has a workforce of 1800 employees. I maintain an employee data base in excel. I want to create a column in the database, where I will be able to know if an employee is due for the first probation assessment after 3 months. If, after being assessed, the employee is not ready for confirmation I would like to indicate in the next column that “Emp. is Not Ready” and after another 3 months the employee should be subjected to a 2nd assessment. If after the first assessment, the employee is deemed suitable for the position, I should indicated ‘Confirmed” in the next column to close the chapter on that particular employee. What functions can I use to know (1) when an employee is due for the first assessment, (2) if employee is not subjected to the first assessment after 3 months that the 1st assessment is overdue, and (3) when the 2nd assessment is due. I have attached part of the database for you to fully understand my problem. Regards Loti The sample spreadsheet attached had some quite lengthy nested IF statements such as… =IF((DATEDIF(C4,TODAY(),”d”))>80,IF(((DATEDIF(C4,TODAY(),”d”)))<91,”Due”,IF(((DATEDIF(C4,TODAY(),”d”)))<180,IF(ISBLANK(E4),”Over due”,””),””))) It took a bit of fiddling and head scratching to work out the logic of the problem. It was interesting because there were several logical tests to consider: How long has the person been employed? Have they been confirmed yet? Have they been assessed yet? Again, I thought, VLOOKUP and his magical pony can save the day. With a relatively simple IF, a VLOOKUP and a super-sneaky IF inside the VLOOKUP we can test for multiple conditions in one sweet package… Column A is Date of Employment. Note that in this test data I fixed “today” to a particular date so the result would not vary over time. One would use the TODAY( ) function for the real thing. The important column is B – status. If they have not been employed for 90 days, they are not due for assessment. If they have been employed 90 to 179 days, and they have not been assessed, their status is “due for first assessment”. If they had been assessed but not confirmed, they would be shown as “Not ready 1”. You can see how complex this is getting. Nested IF was simply not up to the task. This is how I did it… Ignore the fact that DOE entries are manufactured rather than being literal dates. I did this to easily create test data for each possible DOE. Look at column B. If the person has NOT already been confirmed… “A VLOOKUP begins, looking up the person’s length of service. Once it finds the right row in the table, it needs to choose either column 3 (if they have been assessed) or column 4 (if they have not been assessed). The respective cells contain appropriate status information. The choice of column is made in the VLOOKUP formula by the IF statement checking to see if an assessment has been made.” If the person HAS been confirmed, it’s a simple status of “confirmed”. So I hope you can see how once again VLOOKUP can be made to respond to second-level decisions by using different columns for the function’s return value. It’s similar to a couple of the challenges above. Thanks for the challenge, Loti! Download the Excel solution (Excel 2007 format) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Challenge 8I think you’ll like this one. Chris of a company in the U.K. wrote to say: Hi, How would you suggest I set up the attached spreadsheet to avoid so many nested "IF"s" Best Regards, Chris Are you spotting any early problems yet? I’m sure you are. Now for the fun bit – look at the formula in A3… =(IFERROR(INDEX($B$2:$G$2,1,(MATCH(“Ex-Stock”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“1 Week”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“2 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“3 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“4 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“5 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“6 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“7 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“8 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“9 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“10 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“11 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“12 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“13 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“14 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“15 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“16 Weeks”,B4:G4,0))), (IFERROR(INDEX($B$2:$G$2,1,(MATCH(“38 Weeks”,B4:G4,0))),A3) ))))))))))))))))))))))))))))))))))) WOW!And it would have continued unto the crack of doom if he had included all the weeks between 17 and 37 – and thereon! Take a few minutes to try solving it yourself before reading on… As I replied to Chris: Wow, that was one really strangled formula you had there. First problem was that you were mixing numbers and text in cells, which prevents Excel doing numeric comparisons. Secondly, using text like "ex-stock" or "no bid" means nothing numerically to Excel. So, by replacing "ex-stock" with a really low number (0) and "no-bid" to an impossibly high number (999) you can find the lowest value with a simple MIN. Then use MATCH to find the corresponding column number containing that minimum value. Then use index to find the matching value in the list of supplier names. Hope that helps Cheers Mark The result, spookily enough, was very similar to a tongue-in-cheek solution I gave for a dodgy ITA exam question in my 2008 Postmortem Question 12. This is how it looked after my facelift: The revised formula for A3 is far more easily chewable – and it can handle any number of suppliers with no extra code! =INDEX(suppliers,1,MATCH(B3,C3:J3,0)) Note that C2:J2 is named ‘suppliers’ (I love named ranges) and B3 just contains MIN(C3:J3). Originally I had the MIN embedded in the A3 formula like this: =INDEX(suppliers,1,MATCH(MIN(C3:J3),C3:J3,0)) which worked just as well, but I thought it was getting a bit long, and I also thought it would be informative to have the value visible in column B. It just shows that you can hide subsidiary calculations inside bigger formulas, or have them separate. I usually prefer starting with smaller step-by-step calculations. When it’s all working, I can collapse the individual steps into one big formula. Walk-through: The MIN function finds the smallest value in the range. The MATCH function finds which column in C3:J3 contains that value. Finally the INDEX formula looks in the corresponding column in row 1 to get the supplier’s name. See the fixed Challenge 8 spreadsheet (XLS format) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Next challenger please… |
Created 25 July 2006