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
Search JabSto ::

Custom Search