Other Links

This is one of the 33 lessons from the
Tutorial on Excel

Lesson 3-17: Array Formulas in Excel

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 (Chapter 13) and INDEX/MATCH (Chapter 14).

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, less limited and  much easier to handle that VLOOKUP. But there are some things that even INDEX/MATCH alone cannot do.

The other Excel array formula that I use is INDEX/MATCH in array format when I want to lookup a table based on criteria in more than one column. See how it is done in "excel-tutorial-index-match.xls"

This is one of the 33 lessons from the
Tutorial on Excel