Chapter 14: Using Formulas for Matching and Lookups
14
becomes the row argument for INDEX. The year 2012 is matched across the header row, and
since 2011 is the second item, MATCH returns 2. INDEX then takes the 2 and 3 returned by
the MATCH functions to return the proper value.
Using default values for match
Let’s add a twist to our sales lookup formula. We’ll change the formula to allow the user to
select only a region, to select only a year, or to select neither. If one of the selections is
omitted, we’ll assume that the user wants the total. If neither is selected, we’ll return the
total for the whole table.
=INDEX(C4:G10,IFERROR(MATCH(C13,B4:B10,FALSE),COUNTA(B4:B10)),IFERROR
(MATCH(C14,C3:G3,FALSE),COUNTA(C3:G3)))The overall structure of the formula is the same, but we’ve changed a few details. The range
we’re using for INDEX now includes row 10 and column G. Each MATCH function’s range is
also extended. Finally, both MATCH functions are surrounded by an IFERROR function that
will return the Total row or column.
The alternate value for IFERROR is a COUNTA function. COUNTA counts both numbers and
text and, in effect, returns the position of the last row or column in our range. We could
have hard-coded those values, but if we happen to insert a row or column, COUNTA will
adjust to always return the last one.
FIGURE 14.12
Returning totals from the sales data