This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
You Already Know MATCH, Really!
Problem: The author of this book is jamming two functions that I have NEVER heard of on the same page. He is starting to hack me off.
Strategy: Really, if you know and love VLOOKUP, you already know MATCH. Let me compare and contrast:
● The first argument is a lookup value just like VLOOKUP.
● The lookup table is a single column, not a rectangular range.
● You don’t have to specify a column number, so leave off the third argument.
Figure 433 MATCH is a VLOOKUP in disguise.
● The last argument should be FALSE just like VLOOKUP, although most people use zero instead of FALSE.
So far, so good. It is just like a VLOOKUP.
The one difference that seems confusing... MATCH does not return a value from the table. MATCH tells you which row in the table contains the MATCH. I remember reading about this in Excel help and wondering when I would ever have a manager call me up and ask, “Hey Bill, what ROW is that in?" Here is the trick: You will ALWAYS be entering your MATCH inside of an INDEX function. So, back to INDEX.