Microsoft Office Tutorials and References
In Depth Information
Syntax
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.
Figure 12.32. Unlike
Unlike VLOOKUP
VLOOKUP and
and HLOOKUP
HLOOKUP, the aging
, the aging LOOKUP
LOOKUP function
function
can process many lookups in a single array formula.
Search JabSto ::




Custom Search