Microsoft Office Tutorials and References
In Depth Information
you should enclose the LOOKUP in a wrapper function such as SUM to add
all the results from the function.
In Figure 12.32 , a series of invoices appear in rows 4 through 17. A GP%
(gross profit percentage) is associated with each invoice. The sales rep will
earn a bonus depending on the GP% of each invoice, as shown in E6:F10. In-
stead of calculating a bonus for each row, you can calculate a bonus for
all the rows at once. The formula in B1 of Figure 12.32 specifies an array
of B4:B17 as the lookup value. This causes Excel to perform the LOOKUP
14 times, once for each value in the range B4:B17. The formula wraps the
LOOKUP results in a SUM function to add up all the bonus results. To cal-
culate correctly, you must hold down Ctrl+Shift while pressing Enter after
typing this formula. When you press Ctrl+Shift+Enter, Excel adds the curly
braces around the formula. You do not type the curly braces manually.
Typing the curly braces will not work.
Figure 12.32. Unlike
HLOOKUP, the aging
, the aging LOOKUP
can process many lookups in a single array formula.