Microsoft Office Tutorials and References

In Depth Information

**Performing Many Lookups with LOOKUP**

the formula to grab the row number from C6, C7, C8, ... as the formula

gets copied down.

66. Optionally, add a conditional format to cell D5 to highlight the cell

if this formula evaluates to TRUE: =D5>=$A5.

77. Copy the formula from cell D5 to D5:O12.

Figure 12.30.

Figure 12.30.
This performs eight relatively slow

This performs eight relatively slow MATCH

MATCH functions and

functions and

then 96 relatively fast

then 96 relatively fast INDEX

INDEX functions.

functions.

The eight MATCH functions in column C take a similar amount of calculation

time as eight VLOOKUP functions would take for Warehouse 1. The advantage

of this method is that the 96 INDEX functions are much faster calculating

than 88 VLOOKUP functions. Even though this method uses 104 formulas in-

stead of the 96 formulas back in
Figure 12.25
, this method calculates in one-

eighth the time.

Performing Many Lookups with

Performing Many Lookups with
LOOKUP

LOOKUP

Even Excel Help tells you to avoid the old LOOKUP function. However,

LOOKUP can do one useful trick that VLOOKUP and HLOOKUP cannot do
—
it