Part II: Working with Formulas and Functions
references. For named ranges, a1 can be either TRUE or FALSE, and INDIRECT will return
the correct range.
INDIRECT can also return ranges from other worksheets or even other workbooks. However, if it references another
workbook, that workbook must be open. INDIRECT does not work on closed workbooks.
Looking up a value based on a two-way matrix
A two-way matrix is a rectangular range of cells. That is, it’s a range with more than one
row and more than one column. In other formulas, we’ve used the INDEX and MATCH combi-
nation as an alternative to some of the lookup functions. But INDEX and MATCH were made
for two-way matrices.
Figure 14.11 shows a table of sales figures by region and year. Each row represents a region,
and each column represents a year. We want the user to select a region and a year and
return the sales figure at the intersection of that row and column.
=INDEX(C4:F9,MATCH(C13,B4:B9,FALSE),MATCH(C14,C3:F3,FALSE))
FIGURE 14.11
Sales data by region and year
By now you’re no doubt familiar with INDEX and MATCH. Unlike other formulas, we’re using
two MATCH functions within the INDEX function. The second MATCH function returns the
column argument of INDEX as opposed to hard-coding a column number.
Recall that MATCH returns the position in a list of the matched value. In Figure 14.11, the
North region is matched, so MATCH returns 3 because it’s the third item in the list. That
