INDEX/MATCH vs. VLOOKUP

I have a group called Excel Enthusiasts (catchy title, eh?) on LinkedIn that I started several years ago.  It currently has over 16,000 members from over 60 countries.  Recently, one of the leading discussions has been on which is better, VLOOKUP or INDEX/MATCH. 

Clearly, VLOOKUP is by far the most frequently used function in this instance, primarily because it is the more unassuming formula, but also because most Excel users simply don’t understand how to use the INDEX/MATCH combination.

The major drawback of VLOOKUP is it requires a static reference in the form of the first column.  Not very cool.  INDEX/MATCH on the other hand, is much more flexible, allowing you use whichever column you choose for your reference.  Much cooler.

Let’s take a look at a simple example using the Illustration below:

Let’s say you wish to create a Code Retriever.  MATCH returns the Row Number of the location in an array of value you specify. Using the example above, the number “4” is returned by the formula:

=MATCH("Tampa", $A$2:$A$8,0)

INDEX, on the other hand, returns the Value that you identify by row number in an array. Using the example above, “Tampa” is retuned by the formula:

=INDEX($A$2:$A$6,4)

Combining the INDEX and MATCH functions is where the Real Power comes in. Let’s say we want the Code for San Diego. We can set up a Code Retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "141"):

=INDEX($C$2:$C$8, MATCH(E2, $A$2:$A$8, 0))

Using the MATCH and INDEX functions together is truly a Powerful way of extracting data when you need it. Once you try it, you may never go back to VLOOKUP again!