MrExcel.com - Your One Stop for Excel Tips & Solutions

You Already Know MATCH, Really!

April 18, 2022 - by

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.
  • The last argument could be FALSE just like VLOOKUP, although most people use zero instead of FALSE.
The MATCH function is built similar to VLOOKUP. You are looking up A397 in a column of account numbers. False or zero indicate an exact match.
Figure 423. 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.

Problem: I was reading Excel Help for fun the other day and I read about a function called INDEX. Who in their right mind would ever use =INDEX(B4:G22,2,4) to point to cell F6?

=INDEX(B4:G22,2,4) returns the 2nd row and 4th column from B4:G22.
Figure 422. The INDEX function seems useless.

Strategy: You will never use INDEX without using MATCH as either the second or third argument. Use MATCH as the second and/or third argument to calculate which row or column to return on the fly.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Winkler on Unsplash