• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Sum Product Instead of VLOOKUP

E

Excel-Access

Member
I have read that SUMPRODUCT is very powerful and can be used instead of VLOOKUP.

Can someone please guide me or provide a link where I can learn about this technique please?

Thank
 
VLOOKUP and INDEX/MATCH will be far faster than SUMPRODUCT at looking up an item in a list.

SUMPRODUCT is used for crunching lots of numbers together based on conditions. Yes, it conceivably be used in place of VLOOKUP or INDEX/MATCH. But it should not be.
 
VLOOKUP and INDEX/MATCH will be far faster than SUMPRODUCT as they are simply searching or a value based on an index value
Best used when you want to return a single value

Sumproduct is a calculation machine and is great at crunching multiple Ranges/arrays of numbers as well as throwing in conditions for each array
Best used when you want to perform advanced sum's with multiple conditions
Yes it can find a single value but it uses shear grunt to do it
 
I'd add "Best used when you want to perform advanced sums with multiple conditions and you can't use a PivotTable or the more-efficient SUMIFS"
 
SUMPRODUCT has the advantage that it can be used on non-contiguous data and will handle conditions on columns and rows at the same time.

I'm of an age that I can remember Excel before SUMIFS when SUMPRODUCT and the magnificent '*--1' were the only way of doing it.
 
You must log in or register to reply here.
Back
Top