Match plus Index = Power!

You are an Excel Poweruser, and you say you often use the VLOOKUP function, but would like something a bit more versatile and powerful? Look no further than the MATCH and INDEX functions!

If you want something more versatile than VLOOKUP, MATCH and INDEX can serve as your humble servants. Let’s take a look at a simple example using the Illustration above:

For instance, 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))

Once you try this, you will be Amazed! Using MATCH and INDEX functions together, now that’s POWER!