Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

Formulas in column E calculate the weighted values. The formula in E2 is

=D2*B2

Cell B8 computes the GPA by using the following formula:

=SUM(E2:E6)/SUM(B2:B6)

The preceding formulas work fine, but you can streamline the GPA calculation quite a bit. In fact,

you can use a single array formula to make this calculation and avoid using the lookup table and

the formulas in columns D and E. This array formula does the job:

{=SUM((MATCH(Grades,{“F”,”D”,”C”,”B”,”A”},0)-1)*CreditHours)

/SUM(CreditHours)}

Performing a two-way lookup

Figure 8-12 shows a worksheet with a table that displays product sales by month. To retrieve

sales for a particular month and product, the user enters a month in cell B1 and a product name in

cell B2.

Figure 8-12:
This table demonstrates a two-way lookup.

To simplify things, the worksheet uses the following named ranges:

Name

Refers To

Month

B1

Product

B2

Table

D1:H14

MonthList

D1:D14

ProductList

D1:H1