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
 
Search JabSto ::




Custom Search