But, this cool Excel formula doesn’t work on its own. In fact, it’s difficult to think of an application of this Excel formula in isolation. That’s because it’s a great facilitator of other formulae, including its more storied colleagues INDEX, OFFSET and VLOOKUP. Few know that these Excel superstars owe much to a lesser-known Excel formula …
I give you: the MATCH formula. Never heard of it? That doesn’t surprise me. It sits in the background and, unlike Excel pin-ups such VLOOKUP, does not get much attention. Let’s change that today! The MATCH formula is at the heart of so many powerful mechanisms in Excel, so let’s learn a few now …
First, why is MATCH of interest to the Excel programmer? Let’s talk about position control. Excel might be many things to you: a canvas, a prison, a playground, your own personal hell …! It’s certainly one thing to everybody: a grid. Yes, we should not forget this simple idea: that Excel consists of cells arranged in rows and columns: it’s a grid!
Seeing Excel in this way allows you to understand the power of MATCH. If Excel is a grid, we need to navigate it to get things done. This is what position control is: your ability to get around Excel’s grid using the tools available to you, with precision … and without too much stress.
So, MATCH helps because it gives a sense of position. The position of a value in a row or a column. More precisely, it tells you how many ‘rows down’ a column, or how many ‘columns across’ a row, a particular value (known as a ‘lookup value’) is.
Seems simple enough? Good! But have you ever built a MATCH formula? Even beginners can do it – make sure you download the file and give it a go yourself.
By returning position, the MATCH formula synergises beautifully with other formulae in Excel. With VLOOKUP, MATCH allows us to choose a lookup column dynamically when combined with a dropdown menu – as demonstrated in the video.
MATCH is great friends with the OFFSET and INDEX formulae too; in fact, without MATCH, these formulae would be pretty useless! In the video, I show how to use MATCH to facilitate a cool dynamic sum operation, again with dropdown menus to smooth the user experience.
Once you get to grips with MATCH (the challenge is more conceptual than technical,) I am confident you’ll see applications in your own Excel work. Let me know how you get on!
