In Excel 2007, Microsoft thought it would be a good idea to increase the number of times you can nest IF functions from 7 to 64.

S I X T Y – F O U R! Are you kidding me? If you need to nest any more than 7 IFs then you should be using VLOOKUP or INDEX & MATCH.

Every now and again I see a serial IF nester. Sometimes they don’t only nest IFs, they also nest other functions as well.

Just take this 22 IF whopper as an example:

=IF(AND([@[Code]]="No Discount",[@[New_Rate]]=""),[@[Code]], IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],7)="Group"), "Group", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],8)="Group"), "Budget", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],3)="TLZ"), "Travel ", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],2)="TZ"), "Travel ", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],2)="AL"), "Amazon", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],3)="ALW"), "Amazon", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],3)="AMA"), "Amazon", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],2)="LZ"), "Lazy", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],3)="C2C"), "Coast", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],3)="RCG"), "RCG", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],3)="ALP"), "Amazon", IF(AND([@[Code]]="No Discount",LEFT([@[New_Rate]],4)="ALLC"), "Amazon", IF([@[Code]]="No Discount", IF(ISNUMBER(MATCH([@[New_Rate]], Clean!$N:$N,0)),[@[Code]],[@[New_Rate]]), IF(LEFT([@[Code]],2)="TT","TT", IF(LEFT([@[Code]],2)="TB","TT", IF(LEFT([@[Code]],3)="ETW","TT", IF(LEFT([@[Code]],2)="ET","TT", IF(LEFT([@[Code]],3)="RCG","RCG", IF(LEFT([@[Code]],8)="RogerCox","RCG", IF(LEFT([@[Code]],2)="GS","Great South", IFERROR(VLOOKUP([@[Code]], Clean!F:G,2,FALSE),[@[Code]]))))))))))))))))))))))

No, I didn’t make this up. This is a real formula sent to me by one of our members. I’ve changed the references for privacy, but otherwise it’s the real deal.

## The Problem with too many Nested IFs

Aside from the fact that a formula like this is very difficult to read and understand for anyone inheriting the workbook, let alone the person who wrote it, the main problem you’re likely to encounter are performance issues.

If the first IF function evaluates to TRUE, then it’s ok. Excel stops at that point and returns the result and the remaining 21 IFs don’t get evaluated.

However, the cells where every one of the IF functions evaluates to FALSE consume a huge amount of processing power.

Just think about the work Excel has to go through to get to that last IF.

To be clear, it has to evaluate:

- 22 IF functions
- 13 AND functions
- 19 LEFT functions
- 1 ISNUMBER
- 1 MATCH
- 1 IFERROR
- 1 VLOOKUP

**And that’s just in one cell**. Now multiply that by a hundred, or a thousand, or tens of thousands of cells.

And it doesn’t even have to be the last IF that’s the problem. Any more than 2 nested IFs in large workbooks can be a problem.

So, if you’re wondering why your workbooks are large and slow, then take a look at your nested formulas.

## Alternatives to Excel Nested IFs

The alternative to nested IF functions is often a simple VLOOKUP formula.

To recap, the syntax for VLOOKUP is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let’s start off with a simpler nested IF example:

This formula uses IF to return the State for each city (I’ve wrapped each IF onto a separate row for clarity):

=IF([@City]="Sydney","NSW", IF([@City]="Melbourne","VIC", IF([@City]="Adelaide","SA", IF([@City]="Brisbane","QLD", IF([@City]="Darwin","NT", IF([@City]="Perth","WA"))))))

A more efficient way to return the State values is to use VLOOKUP to find the city (lookup_value) in the table_array and return the state:

**Tip:** For bonus efficiency points **sort the city column** in alphabetical order. Then you can use the VLOOKUP sorted list method i.e. use 1 or TRUE for the last VLOOKUP argument; [range_lookup]:

`=VLOOKUP([@City],TableArray,2,`**1**)

**Caution:** with the range_lookup argument set to 1 or TRUE you must ensure every value you want to lookup is in the table_array, because this VLOOKUP will not return an error if your lookup_value isn’t found. Instead it will return the next closest match.

An even better alternative to VLOOKUP is INDEX & MATCH. If your workbook is still slow, then replace VLOOKUP’s with INDEX & MATCH. Make sure the MATCH value is in a helper column i.e. not another nested formula.

## Download the workbook

## Alternatives to Multiple Nested Functions

The 22 IF whopper formula sent in by one of our members needs more than just a VLOOKUP to replace it. Unfortunately, I don’t have the data so I can’t demonstrate a solution, however I can describe the approach.

Simply put, the formula needs to be broken into separate steps:

**Step 1:** Insert a helper column to extract the lookup_value for each row. That is, put the AND and LEFT parts of the formula into a helper column, so you can then use VLOOKUP to find that value in the lookup table_array.

Now, I know some of you pride yourselves on not needing helper columns, but **using helper columns doesn’t make you a formula amateur**.

In fact, often it’s much more efficient to use a helper column than to write formulas like the one above.

**Step 2:** create a lookup_table that contains the lookup_values and the result you want returned. Remember, use INDEX & MATCH if VLOOKUP is still slow.

## More Performance Tips

If you’re often wrangling sluggish Excel workbooks then it will pay to spend some time perusing fellow Excel MVP, Charles Williams’ website.

Charles has also developed a toolset designed to help you understand, manage and improve workbook performance. It’s called Fast Excel and includes:

**Profiler** - gives you a comprehensive set of tools focussed on finding and prioritising calculation bottlenecks. If your spreadsheet takes more than a few seconds to calculate you need FastExcel profiler to find out and prioritize the reasons for the slow calculation

**Manager** - contains tools to help you build, debug and maintain Excel workbooks.

**Speed Tools** - provides you with a state-of-the-art tool-kit to help you speed up your Excel Calculations

If you have performance tips please take a moment to share them in the comments below.

## Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.

David Murphy says

Good post and it highlights that, quite often, Excel can become more than a handful to a) maintain and b) understand.

An alternative solution, of course, would be to write a custom function – although I do appreciate that not all users may want to dive into a bit of VBA. But the advantage with the VBA/Custom Function is that you can build a much more readable “formula” due to the structured approach of VBA and have as much complexity as you want without cluttering up your worksheet.

Mynda Treacy says

Thanks, David.

UDF’s are an option but they often perform worse than the built in functions. Plus, like you say, you need to know how to write VBA 🙂

Peter Buyze says

Just as an aside, but not quite: the reason one would prefer INDEX & MATCH to VLOOKUP is because the latter can only return data to the right of the reference column. But that statement is not quite right.

Ryan Dube (MakeUseOf) posted an article 3 years ago showing how you can nest the CHOOSE function within VLOOKUP to make VLOOKUP return data located left of the reference column. Here is the article http://www.makeuseof.com/tag/3-crazy-excel-formulas-that-do-amazing-things/.

Mynda Treacy says

Yep, I posted a tutorial on using CHOOSE with VLOOKUP 5 years ago ;-p

http://www.myonlinetraininghub.com/excel-choose-function

Still, I’d recommend INDEX & MATCH over VLOOKUP and CHOOSE.

Sunny Kow says

Having taught Excel to 100’s of my colleagues, somehow VLOOKUP is overwhelmingly their favorite choice (possibly 99%). My guess is they prefer using one formula instead of two and speed is not a concern to them. I too prefer VLOOKUP and rarely need INDEX & MATCH in my work. Writing a UDF would be my last choice.

Mynda Treacy says

VLOOKUP is my all time favourite function 🙂