INDEX + MATCH = INFORMATION

There are many ways to look up information in tables and databases. Excel provides us with functions such as VLOOKUP, DSUM, DGET, SUMPRODUCT, and several others. There are pluses and minuses when working with any of these tools, but in certain circumstances one or more of them can be an excellent solution. It’s all about returning Information in the way you want it.

Flexibility is always a plus, of course. When it comes to Versatility, the combination of using INDEX and MATCH can be hard to beat! This is especially true when comparing these powerful tools with the decidedly more limited VLOOKUP and HLOOKUP functions.

 For instance, using this combination alleviates the need to stay locked into the first column for a reference point. As I discussed in this blog in years past, a simple example goes a long way in illustrating this piece of Excel Magic:

 INDEX and MATCH can serve as your able-bodied 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)) 

Using the INDEX and MATCH functions together enables the crafty Excel Guru to extract information from a table or database of nearly any size or dimension. It gives you the ability to sidestep some of the shortcomings of other techniques, and lets you Have it Your Way!