July 16, 2004

Multiple Lookup

The SUMPRODUCT function must be one of the most useful functions available in Excel. I also think it is one of the most interesting (Even some of the leading Excel experts seem excited on finding new ways to use it's potential to the max)

Today's demo is rather easy. I have names and dates in the columns shown in the picture below.

Some of the names are the same, some cells are blank. Combinations of CONCATENATE, INDEX, MATCH and OFFSET etc will also get the correct result, but SUMPRODUCT just by itself will get the results without the need to use nested formulas such as in the next picture (Incidentally using SUM and IF is a good alternative to remember, but don't forget that limitations on nesting formulas can make life difficult later on, even if there are ways to get around this)

So how does SUMPRODUCT work? A simple explanation would be to say that the "*" operator acts as AND. It looks for a matching combination of the First, Middle and Last Names to locate the correct Age.

(The "+" operator acts as OR so the result in the example above would be 78 had I so used it instead of the "*" operator)

For some further information on SUMPRODUCT I suggest xlDynamic.com as a very useful reference.

Have a nice weekend.