Excel MATCH Function

The MATCH function and other intermediate-to-advanced functions are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

MATCH Function 

The MATCH function serves a single purpose. Its purpose is to find the relative position in a column or a row of a lookup value. 

In some ways, the MATCH function is very similar to VLOOKUP. You're looking up information in a particular range. The difference here is the range can only be a column or a row. One column or one row. It cannot be multiple columns in multiple rows. 

That's a key distinction for the MATCH function. 

Match type is pretty much the same as range lookup in VLOOKUP. You'll either want an exact match or an approximate match. An approximate match will be either one or you get the added benefit of choosing negative one. One finds the largest value, based on ascending order, largest value without going over. And then the descending order is the opposite. You'll find the smallest value without going over, and that is in descending order. Most of the time, though, you'll choose zero for an exact match. 

So the other thing you need to know about the MATCH function is it's not going to return a value like VLOOKUP. It's just going to return a position number. So it's not really useful unless you use it with other functions. You can use it with VLOOKUP. You can also use it with INDEX where it's commonly referred to as INDEX MATCH. 

Putting It in Practice 

All right. So let's take a look at how this would work because other than me just telling you this, you'll actually have to see it put in practice. So I'm going to select the cell that's to the right of cantaloupe. Cantaloupe is my lookup value. I want to find the position of cantaloupe within the row below. So I'll type in equal, MATCH. 

I'm going to select cantaloupe, I'll enter a comma, and then I want to select the row that contains cantaloupe. I'll enter a comma. Excel reminds me here that if I want an exact match, I'll choose zero for exact match. I'll press tab. When I press enter, I get five. 

Now someone looking at this might think this is a useless endeavor. Why would I write a function to tell me something I can figure out just by looking at the sheet? I can count from one to five and see that cantaloupe is in the fifth position. 

Now counting, in this case, is very similar to what you do in a VLOOKUP. When you get to the column index number, you count the number of columns you need to move across the table to find the field that you're looking for. 

Well, this is an opportunity to use MATCH instead of manually counting. The other benefit of using MATCH is if in the VLOOKUP, you've used the MATCH function for the column index number, if someone inserts a column in your table, the MATCH function will automatically update to reflect the new column index number. So that would make the VLOOKUP dynamic, so that's another useful benefit of using the MATCH function. 

Exercise

In class, he would tell students, if you feel like you have an understanding of this, please tackle Task 2. 

Use the MATCH function to find the row position number that corresponds to France. I'll type in equal, MATCH. I will select my lookup value, which is France. I'll enter a comma. Then I will go over and select the entire column. 

Again, I want an exact match, so that will be zero. When I press enter, the answer is seven. And if I take a look at the column, the United States is one, then two, three, four, five, six, seven. And that is accurate. 

Recap

So that is the MATCH function. Again, you'll most likely use the MATCH function with other functions to find a position number of a value in either a column or a row.