Excel Lesson 21 of 30: Array Formulas

For years I have been promoting the use of ARRAY FORMULAS (SUM) in Excel spreadsheets as the magic formula that solved all the problems when organizing data into reports. When I discovered the SUMPRODUCT formula, I completely abandoned the ARRAY FORMULAS. The SUMPRODUCT formula does the same thing as the ARRAY FORMULA (SUM) but is more accessible to all and much easier to use.

Array formula have been created when computers were much less powerful and memories much smaller. Computers have evolved so much that array formulas are not really useful anymore. Some of them though are indispensable. Here are two examples of things you cannot do without using array formulas.

Before getting into array formulas be sure that you have discovered the extremely powerful SUMPRODUCT (Lesson 17) and INDEX/MATCH (Lesson 18).

MAX

To enter an array formula in a cell you don't simply use the "Enter" key but you hold the SHIFT and CTRL keys while you click on ENTER. If you do it right you will see special parentheses appear at each end of the formula { }.

Among the functions that are used in array format are MAX and MIN. For example:

In the table above when is the last date at which John Spencer reported? 5/11/2006 The array formula for that is:
{=MAX((A2:A14="John")*(B2:B14="Spencer")*(C2:C14))}

What is the maximum weight that John Spencer has reached? 162
{=MAX((A2:A14="John")*(B2:B14="Spencer")*(D2:D14))}

INDEX/MATCH

INDEX/MATCH formulas by themselves are very powerful and less limited much easier to handle that VLOOKUP. But there are some things that even INDEX/MATCH cannot do.

The other Excel array formula that I use is INDEX/MATCH when I want to lookup a table based on criteria in more than one column.