This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Optimize Formula Calculation Speed – Part 7: Multiple Criteria Lookups

Optimize Formula Calculation Speed

Excel Optimize Formula Calculation Speed

Part 1: Introduction – Part 2: Timer – Part 3: Volatile formulas – Part 4: Lookup formulas – Part 5: Sorting source dataPart 6: Table or List? Part 7: Multiple criteria lookups – Part 8: Sum with multiple criteriaPart 9: Other factors Part 10: Summary

 

Part 7: Multiple Criteria Lookups

In the previous part, we saw that calculation times are significantly reduced when the source data is laid out as a table, rather than a list. If we were using the SUMPRODUCT formula on a list we could obtain the same result in just 2% of the time.  All we need to do is change our formula method and the source data structure into a table.

So far in this series, we have assumed that our data has been based on a single criterion (or two criteria in the case of a table).  Our data set actually has 6 criteria which could be used. It is those 6 criteria which combine together to give us our list of 42,941 unique records.  In this part, we will look at the multiple criteria lookups available and which is the fastest.

 

Setting up the test


Advertisement:

The formulas we will be considering are:

INDEX/MATCH – by using a helper column

SUMIFS

INDEX/MATCH (Array) – Method 1

INDEX/MATCH (Array) – Method 2


Advertisement:

SUMPRODUCT

VLOOKUP/CHOOSE

SUM (array)

We will be using the same data as previous parts.  As our data set is unique, and based on numerical values so SUMIFS and SUM (Array) are also available for returning the same value as a lookup formula.  If our data were not unique or based on less than the 6 criteria we may need to use different formulas.  The workbook can be downloaded here.

The results

Excel screen shot of times for multi-criteria lookup formula

Just take a few seconds to look at the results.


Advertisement:

The key learning for me is that using a simple INDEX/MATCH with a helper column is significantly faster than all the other formulas. Our helper column only needs to recalculate when the source data changes.  As a result, the helper column calculations can achieve faster times.

SUMIFS was a bit of a surprise. I know that this formula was built to be fast when summing with multiple criteria, but I wasn’t expecting it to be as fast as it was. Whilst it may be 5 times slower than using an INDEX/MATCH with a helper column, but it is also 5 times faster than the best of the other formulas.  This formula is only available because we are looking up numbers, if the lookup result had been text, then this would have resulted in an error.

The SUM (Array), SUMPRODUCT and INDEX/MATCH (Array) methods all perform at roughly the same speed. In the circumstance where you need to use a lookup formula (without a helper column) on unique data, one of these would be suitable.  INDEX/MATCH (Array) – Method 1 is slightly faster than the others.  In my opinion, it is slightly easier to understand, and also works on text,  so would be my preferred option.

The VLOOKUP/CHOOSE (Array) formula was the slowest of all the formulas tested.

 

Conclusion

I must admit, I can be a bit of a formula snob. I have a tendency to look down on spreadsheets which are created with helper columns.  If I can do in one cell what others need a helper column for then surely my spreadsheet must be better, right?  Well, maybe not.  My formula may be more complex and achieve everything in a single cell, but it also has the potential to be significantly slower.  If we have a need for speed, then helper columns should definitely be a consideration.