Excel: You Already Know MATCH, Really!

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.
  • The last argument could be FALSE just like VLOOKUP, although most people use zero instead of FALSE.

  1. MATCH is a VLOOKUP in disguise.

    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.