Microsoft Office Tutorials and References

In Depth Information

**Using MATCH and INDEX to Fill a Wide Table**

Using

Using
MATCH

MATCH
and

and
INDEX

INDEX
to Fill a Wide Table

to Fill a Wide Table

The lookup functions VLOOKUP, HLOOKUP, and MATCH can be very

processor-intensive when the lookup table contains hundreds of thousands

of rows.

Back in
Figure 12.25
,
Excel had to do 96 VLOOKUP functions. However, after

Excel figured out the position of item G598 in the lookup table for cell C6, it

had to go back through exactly the same steps for cell D6, E6, F6, G6, and so

on. You made Excel find exactly the same item 12 times, which is a very slow

process.

If the recalculation times are taking too long, you should consider using one

MATCH per row to find the relative row number and then using 12 speedy

INDEX functions to fill in the values in that row.
Figure 12.30
illustrates a

problem where you can use this trick. In this case, the list of inventory items

is 14,000 rows. Here
’
s what you do:

11. Copy the range of warehouse names from B16:M16 to D4:O4.

22. In cell C5, enter =MATCH(B5,$A$17:$A$14056,0)

=MATCH(B5,$A$17:$A$14056,0). This formula finds an

exact match for C529. The answer 8005 means that product C529 is on

the 8,005th relative row of the lookup table.

33. Copy the formula in cell C5 down to C12. Each of these eight MATCH

functions will take just about as long as the equivalent VLOOKUP

function would take.

44. As you build the INDEX function in D5, be careful that the array

range encompasses the same rows used in the MATCH function. Use

dollar signs before the row numbers, but not before the column let-

ters. Start the formula in cell D6 as =INDEX(B$17:B$14056

=INDEX(B$17:B$14056.

55. Make the next argument the relative row number within the lookup

range. This is the value from column C, so use $C5. If you type CC6 and

then press the F4 key three times, Excel adds the dollar sign before

the C in C5. The formula =INDEX(B$17:B$14056,$C5) contains a brilli-

ant combination of mixed references. The $17 and $14056 ensure that

you are always retrieving values from rows 17:14056. The lack of a

dollar sign before both B
’
s allows the formula to retrieve from

columns C, D, E, ... as the formula gets copied across. In the second ar-

gument, the $C makes sure that the INDEX always uses the row number

from column C. The lack of a dollar sign before the 5 in $C5 allows