Thursday, September 23, 2010


You say you like using the VLOOKUP function, but would like something a bit more versatile and powerful? Look no further than the MATCH and INDEX functions!

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("Sacramento", $A$2:$A$6,0)

INDEX returns the value that you identify by row number in an array. Using the example above, “Sacramento” is retuned by the formula:


Using the INDEX and MATCH functions In Combination, we can set up a code retriever as shown in cells E1:F2, by inserting the following formula in cell F2 (This will return "126"):


Using MATCH and INDEX functions together, now that’s POWER!

No comments: