11. Match and Index functions complement each other
“The INDEX function is one of the most powerful but also one of the most complex functions in Excel,” Zelman said. Both INDEX and MATCH are Lookup functions in Excel, so whatever comfort you have with VLOOKUP or HLOOKUP will be useful here.
Big picture: you use MATCH to find the coordinates, and you use INDEX to apply the coordinates to find a value.
The Match function returns the relative position of an item within a column of data (or a row of data). The syntax is MATCH(lookup_value, Lookup_vector, Match_type). Note I say “Lookup_vector” in order to emphasize the search occurs over a 1 X N area or an N X 1 area, but not, say, a 2 x N area. Repeat: MATCH works only with 1 X N or N X 1 array.
The screen capture below is an example of its use; the function returns the number of the row where a match has been found. (Answer is 11 rows below the top of the array.)
The basic syntax is INDEX(data array, number of row, number of column) and then it will return the value of whatever is in that one cell of where the row and column intersect. Note that the Data array can be N X M in size.
12. Video resources abound
This last one is not a specific trick; it’s a general recommendation. Rather than go on too long about the lovely pairing of these two look-up functions, I recommend a good video about INDEX and MATCH.
There are many YouTube videos to show the particulars of Excel functions. Sometimes that’s all you need or want.
However, if you’re looking for a systematic approach, consider a day-long hands-on seminar in your city. In this case, I attended the seminar put on by the Marquee Group through the CFA Society Toronto. ª