Chapter 14: Using Formulas for Matching and Lookups
14
In the example shown in Figure 14.13, when SUMPRODUCT gets to row 12, it multiplies 1 * 1
- 697,697. That number is summed with the other rows, all of which are zero because they
contain at least one FALSE. The resulting SUM is the value 697,697.
Returning text with SUMPRODUCT
SUMPRODUCT works this way only when we want to return a number. If we want to return
text, all of the text values would be treated as zero, and SUMPRODUCT would always return
zero.
However, we can pair SUMPRODUCT with the INDEX and ROW functions to return text. If we
want to return the manager’s name, for example, we could use the following formula:
=INDEX(D:D,SUMPRODUCT(($B$3:$B$45=H5)*($C$3:$C$45=H6)*(ROW($E$3
:$E$45))),1)Instead of including the values from column E, the ROW function is used to include the row
numbers in the array. SUMPRODUCT now computes 1 1 12 when it gets to row 12. The
12 is then used for the row argument in INDEX against the entire column D:D. Because the
ROW function returns the row in the worksheet and not the row in our table, INDEX uses
the whole column as its range.
Finding the last value in a column
Figure 14.14 shows an unsorted list of invoices. We want to find the last invoice in the list.
A simple way to find the last item in the column is to use the INDEX function and count
the items in the list to determine the last row.
=INDEX(B:B,COUNTA(B:B)+1)FIGURE 14.14
A list of invoices