Match Function: Finding Positions in Rows and Columns

VLOOKUP and the 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 and multiple rows. So 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 1 or you get the added benefit of choosing -1.

1 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 0 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. 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 into practice. So I'm going to select the cell 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 =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.

The cell reminds me here that if I want an exact match, I'll choose 0 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 1 to 5 and see that cantaloupe is in the 5th position. Now counting in this case is very similar to what you do in 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. In class, we 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 =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 would be 0. When I press ENTER, the answer is seven.

If I take a look at the column, the United States is 1, then 2,3, 4,5, 6,7, and that is accurate. 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.