Excel Lookup Functions
How to Fight Spreadsheet Hell with Three Excel Lookup Functions
Do you spend hours each period turning raw data into useful information? You can fight this type of Spreadsheet Hell with
help from three key Excel functions.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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 INDEXMATCH. 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 INDEXMATCH
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 referencereturning 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 referencereturning 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 topleft cell of
the result from the topleft cell of the reference.
cols: the number of columns to shift the topleft cell of
the result from the topleft 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, $A71, BegMoIndex1, 1,
CurMoIndexBegMoIndex+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: $A71
(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: BegMoIndex1
(As with the rows argument, we subtract 1 to reference
where the leftmost cell for the YTD calculation must begin.)
height: 1
(We want the resulting reference to be only one row high.)
width: CurMoIndexBegMoIndex+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.
Excelfriendly 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!
Take Your Next Steps
If you're looking for additional help with
this topic, I can help you in three ways. To learn
more, see
Excel Training, Coaching, and Consulting.
Tags: #index,
#match
