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.