(I wrote this long ago, and there’s much to be added. It’s high on my Update list. Charley)
Many Excel users build their reports like a carpenter might build a piece of furniture.
Many Excel users begin with the raw material: data downloaded from a relational database and imported into Excel. Then they often sort the data. Filter it. Add columns of calculations. And massage it in other ways to produce their reports.
The whole process could take hours.
Instead, it often is much easier to maintain the data in an Excel database and use several key spreadsheet functions to do the work. This approach won’t always be possible; but when it is, it can save many hours of work.
This approach typically uses three key spreadsheet functions.
The MATCH Function
The MATCH function looks up a value in a row or column and returns the relative position of the item. That is, if the item is the fourth position in the list (the “lookup_array” below), then MATCH returns the value 4.
The function takes this form:
=MATCH(lookup_value, lookup_array, match_type)
- lookup_value: the number or text value that you’re looking for.
- lookup_array: the list of values to search.
- match_type: the values -1, 0, or 1, which specify the type of search to perform. Because the match_type of zero is the only one that doesn’t require the lookup_array to be sorted, this article discusses only that type.
To illustrate, suppose we assign the name Products to the range A3:A7 in this figure. We can then enter the formula:
=MATCH(“coats”, Products, 0)
Excel returns the value 3, which means that “coats” is the third item in the list. (Notice that MATCH is not case sensitive.)
Similarly, if cell D9 contained “Ties”, this formula…
=MATCH(D9, Products, 0)
…would return the value 4.
By itself, MATCH seldom returns a useful result. By when used with one of the next two functions, it can be extremely useful.
Excel’s INDEX Function
The INDEX function returns a reference at the intersection of a specified row and column within an array or range. It takes two forms:
=INDEX(array, row_num, column_num)
=INDEX(reference, row_num, column_num, area_num)
- array: an array (or range) of cells, or an array constant.
- row_num: the row’s index value from which to return a reference.
- column_num: the column’s index value from which to return a reference.
- reference: an Excel reference to one or more ranges.
- area_num: a number that specifies which reference from which to return a reference.
This article will discuss only the first form.
Suppose we have the following database of sales by month, and that we assigned these range names:
- Products: $A$3:$A$7
- SalesMonths: $B$2:$L$2
- SalesData: $B$3:$L$7
To report August sales, as shown below in yellow, we could add a Report worksheet to our database workbook. Then we could enter the values and formulas shown for the following cells:
B3: 8/1/2006
Named CurMo, the report date that we change monthly.
B4: =MATCH(CurMo, SalesMonths, 0)
Named CurMoIndex, the index number for the current report date.
A7: =MATCH(B7, Products, 0)
A8: =MATCH(B8, Products, 0)
A9: =MATCH(B9, Products, 0)
The index numbers for the products shown.
B5: =”Sales, “&TEXT(CurMo,”mmmm yyyy”)
The report title.
C7: =INDEX(SalesData, $A7, CurMoIndex)
C8: =INDEX(SalesData, $A8, CurMoIndex)
C9: =INDEX(SalesData, $A9, CurMoIndex)
Formulas that return the values for the month and products specified, as determined by the MATCH functions.
Using a combination of INDEX and MATCH — as shown in the formulas for cells C7, C8, and C9 — usually is referred to as using INDEX-MATCH. It’s a very common technique for creating reports that can be updated easily.
To create September’s report, we would merely need to update the database with new data, update the date in cell B3, and then recalculate. Any number of reports could use INDEX-MATCH formulas against the same data.
How to Test an Excel Reference
As you create your INDEX and OFFSET functions, you often will want to know what cells your functions are referencing. There’s an easy way to do this.
1. In your formula bar, first select the part of a formula that returns a reference you want to test. For example, with the three INDEX functions shown above, you would select the entire formula. (It doesn’t matter whether you include or exclude the equal sign.)
2. With the reference-returning section of your formula selected, press Ctrl+C to copy that text to your clipboard.
3. Press ESC to return to the Ready mode.
4. Press F5 to launch the Go To dialog box.
5. Press Ctrl+V to paste the reference-returning section of your formula into the Reference edit box.
6. Choose OK.
When you do so, Excel will select the range that your reference specified, if it’s a legitimate reference.
After you examine where your formula is referencing, you probably will want to return to the original cell. To do so, press the F5 key, then press Enter.
Excel’s OFFSET Function
The OFFSET function works much like INDEX, because it typically uses results from a MATCH function to return a reference. It takes this form:
=OFFSET(reference, rows, cols, height, width)
- reference: a range in an Excel spreadsheet.
- rows: the number of rows to shift the top-left cell of the result from the top-left cell of the reference.
- cols: the number of columns to shift the top-left cell of the result from the top-left cell of the reference.
- height: the number of rows to return.
- width: the number of columns to return.
To illustrate, here’s the sales data again:
And here’s the entire report:
This view shows that we’ve added the following values and formulas:
B1: 1/1/2006
Named BegMo, this is the first month of the current fiscal year.
B2: =MATCH(BegMo, SalesMonths, 0)
Named BegMoIndex, this is the index value for BegMo date.
D7: =SUM(OFFSET(SalesData, $A7-1, BegMoIndex-1, 1, CurMoIndex-BegMoIndex+1))
This formula is slightly more complicated than the INDEX formulas. To understand how it works, let’s look at the OFFSET function one argument at a time:
- reference: SalesData
(This is the same reference that INDEX used above.) - rows: $A7-1
(Remember that the rows argument of the OFFSET function specifies how many columns to move, while the MATCH function returns an index value. To convert from an index value to an offset value, we subtract 1.) - cols: BegMoIndex-1
(As with the rows argument, we subtract 1 to reference where the left-most cell for the YTD calculation must begin.) - height: 1
(We want the resulting reference to be only one row high.) - width: CurMoIndex-BegMoIndex+1)
(This width needs to be the number of months to be reported in the current fiscal year. This value is equal to the formula shown here. To convince yourself of this fact, suppose the current month is January, 2006, as shown in the database shown above. This would give us the value 3 minus 3 plus 1, which is the number of months to be reported.)
As with INDEX, the OFFSET function automatically adapts as you change the date shown in cell B3 of your report.
Fighting Spreadsheet Hell
Depending on your data, it might take some work to set up an maintain a spreadsheet database. But if you maintain your database in one or more separate workbooks, any number of report workbooks can reference the data.
If your company is too large to rely extensively on spreadsheet databases, you might want to consider a structurally similar solution. Excel-friendly OLAP products provide functions that work much like MATCH, INDEX, and OFFSET.
But in either case, the general approach is the same: You create spreadsheet reports that return data from a structured database. Then, each time you create your report for a new period, you merely specify a new date, recalculate your workbook, and print.
And that’s an excellent way to fight Spreadsheet Hell!
