Part II: Working with Formulas and Functions
Figure 14.12 shows the same sales table, but the user has left the Year input blank. Since
there are no blanks in the column headers, MATCH returns #N/A. When it encounters that
error, IFERROR passes control to the value_if_error argument, and the last column is
passed to INDEX.
Finding a value based on multiple criteria
Figure 14.13 shows a table of departmental budgets. When the user selects a region and
department, we want a formula to return the budget. We can’t use VLOOKUP for this for-
mula because it accepts only one lookup value. You need two values because the regions
and departments appear multiple times.
You can use the SUMPRODUCT function to get the row that contains both lookup values as
follows:
=SUMPRODUCT(($B$3:$B$45=H5)*($C$3:$C$45=H6)*($E$3:$E$45))
FIGURE 14.13
A table of departmental budgets
SUMPRODUCT compares every cell in a range with a value and returns an array of TRUEs and
FALSEs depending on the result. When multiplied with another array, TRUE becomes 1, and
FALSE becomes 0. The third parenthetical section in the SUMPRODUCT function does not
contain a comparison because that range contains the value we want to return.
If either the Region comparison or the Department comparison is FALSE, the total for that
line will be 0. A FALSE result is converted to zero, and anything times zero is zero. If both
Region and Department match, both comparisons return 1. The two 1s are multiplied with
the corresponding row in column E, and that’s the value returned.
