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.
I can also recommend a very clear written description of using INDEX-MATCH to solve an everyday problem like data retrieval for paired data such as name, date of birth.
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. ª
Click here to view the first two tricks.
Click here to see the list of 12 tricks.
Click here to browse the Spreadsheeto website, which is full of handy tips on Excel.
Click here to browse the Guru99 website, which contains free Excel tutorials.