What is the benefit of using Index Match (with Aggregate) over SumProduct?

What is the benefit of using Index Match (with Aggregate) over Sum Product?
We solved the look up problem in question  >  http://www.experts-exchange.com/questions/28688174/How-to-Filter-an-Excel-list-using-a-formuls-in-a-cell.html#a40821462
A second solution was posted after the fact which appears to work as well.
It raises the question which method is better for various circumstances?
SumProduct-vs-IndexMatchAggregate.xlsx
Allen PrinceTraining DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Excel problems frequently may be solved with several different approaches. Sometimes, one approach is clearly better than another. Other times, it is really a matter of user choice.

My preference of solution types when posting solutions to questions posted in forums is as follows:
1. Regular (non-array entered) formulas are best
2. An array-entered formula is OK, but some users find them confusing--especially formulas that are array-entered over a range of cells. Array-entered formulas may compute slowly when handling large amounts of data. If so, consider a regular formula using an auxiliary column to speed things up.
3. Macros or user-defined functions can make a complicated problem look simple to the user--but macros must be enabled when the workbook is opened
byundtMechanical EngineerCommented:
After looking at your workbook, I'd suggest going with either a SUMIFS or SUMPRODUCT type of formula. SUMIFS will be easier to understand for most people. Furthermore, SUMIFS will limit its action to cells in the used range of the worksheet, whereas SUMPRODUCT will evaluate the Boolean expressions to the very last row of the worksheet if you specify entire columns. In such cases, SUMIFS is faster than SUMPRODUCT.
=SUMIFS($D$7:$D$15,$B$7:$B$15,LEFT($F20,1),$C$7:$C$15,LEFT(G$19,FIND("-",G$19)-1))

You made life hard for yourself by using header labels that didn't match your data. Had you not done this, the SUMIFS would be a plain vanilla formula like:
=SUMIFS($D$7:$D$15,$B$7:$B$15,$F26,$C$7:$C$15,G$25)
SumProduct-vs-IndexMatchAggregate.xlsx
barry houdiniCommented:
There are some scenarios where only one of the suggested approaches would work.

SUMPRODUCT will only return a numeric result, as will AGGREGATE, so if you wanted to retrieve a text value rather than a numeric value then INDEX/MATCH would be the best approach, but conversely the INDEX/MATCH version can't sum multiple rows so if you wanted to do that you'd need SUMPRODUCT (or SUMIFS as Brad suggests).

Another lookup type formula (which can also return text) could also be used, e.g.

=LOOKUP(2,1/($F14=$B$7:$B$15)/(G$13=$C$7:$C$15),$D$7:$D$15)

regards, barry

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Allen PrinceTraining DeveloperAuthor Commented:
This makes sense as it is in context.
one more piece of the puzzle.
in cells H14:H16, how does AGGREGATE affect the calculation?
byundtMechanical EngineerCommented:
AGGREGATE is a function introduced with Excel 2010. If you still have some users in your organization with Excel 2007, you will want to avoid its use.

In addition, the AGGREGATE formula needs to be array-entered. As previously mentioned, I prefer regular formulas when possible.
barry houdiniCommented:
AGGREGATE seems a bit superfluous in this context. AGGREGATE with 1 as the first argument will average the data, but INDEX/MATCH will only return a single value anyway so the average is just the same as that value.

Perhaps the idea is to get rid of any error, but it doesn't even do that.

AGGREGATE is a useful function, used in the right context but I wouldn't use it here

regards, barry
Allen PrinceTraining DeveloperAuthor Commented:
Once again the Experts have nailed it!
Thanks People!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.