Transcend VLOOKUP!

You want to look up information in a table or database, and you wonder, “Which is better, VLOOKUP or the combination of INDEX and MATCH”?

Without a doubt, VLOOKUP is the most frequently used function in this instance, primarily because it is the more familiar formula for most users, but also because most Excel users simply aren’t aware of the ease and benefits of the INDEX/MATCH combination.

The major drawback of VLOOKUP, of course, is it requires a static reference in the form of the first column. (Who needs that?!?!) INDEX/MATCH on the other hand, is more Flexible, allowing you use whichever column you choose for your reference. Rock on!

For example, look at the illustration of using the INDEX/MATCH combo below:

Let’s say you want to create a Code Identifier in cell F2.  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 Magic 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 and Versatile way of extracting data. Transcend VLOOKUP! There are better tools in town!